Current time in Xcelsis using Yahoo time service

4

If you need the current time in a dashboard, the Excel formula is NOW() is useful for many scenarios. If the local PC time is not adequate I found a slick Yahoo web service that will provide me with a single XML node with current time:

http://developer.yahoo.com/util/timeservice/V1/getTime.html

1. First I used XML Maps in Excel and Xcelsius to import the current time from the yahoo service.

2. The next problem is that the data is a UNIX timestamp, so I found a formula to fix this:
=C5/(60*60*24)+1/1/1970 WHERE C5 is the time retuned from the Yahoo service.

3. The results are in GMT so you may need to make adjustments.. In my requirement for PST, I took the resulting time and subtracted “7:00:00 AM” which is how you subtract 7 hours in Excel.

4. I took it one final step to subtract the expiration date from current time and then format it as “dd:hh:mm” This tells end users not the current time, but rather the difference from current time the expiration.

Hopefully this time I spent figuring this out will save you a little time of your own building dashboards.

CLICK HERE for excel source file. The Yahoo web service is not included because I couldn’t add it without giving up my ID:

Share.

4 Comments

Leave A Reply