With a recent request, I needed to provide a countdown for a dashboard contest that could not be hacked. If you need the current time in a dashboard, the Excel formula is NOW() is useful for many scenarios. The problem NOW(), is a local PC time change will modify the dashboard time.
The solution ti this problem is simple! I found a slick Yahoo web service that will provide me with a single XML node with current time. With the server time as my baseline, I can then create our counter now used on the Experience SAP Baseball page.
CLICK TO VIEW THE EXPERIENCE SAP BASBALL PAGE AND CHECK OUT THE CONTEST
1. Fist I had to sign up to use the Yahoo timer web service
http://developer.yahoo.com/util/timeservice/V1/getTime.html
2. Next I used XML Maps in Excel and Xcelsius to import the current time from the yahoo service.
3. 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.
4. 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.
5. 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:
1 Comment
