My Last Tweet: San Diego
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:
@ Umar: I will get something up sometime today.
Here you can see this solution in action on the SAP Experience baseball site:
http://experience.sap.com/baseball/challenge.html
Here is the source file:
http://ryangoodman.net/blog/media/timer/timer.xls
Can you load the source XLF file with this topic?
Thanks Ryan for the xls. Is it possible to load XLF as well?
Magnificent web site. A lot of useful info here. I am sending
it to a few friends ans additionally sharing in delicious.
And naturally, thanks for your effort!