Skip to Content

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:

To report this post you need to login first.

1 Comment

You must be Logged on to comment or reply to a post.

  1. Matthew Youngman
    G’day Ryan – I like what you’ve been able to do here. Have you thought about how you would deal with Daylight Savings (not sure if your timezone has Daylight Savings?).

    In Sydney we have to deal with Daylight Savings times shifting our clocks forwards and backwards over Summer an hour each year.

    Cheers!

    (0) 

Leave a Reply