Max, a visitor here, recently left a comment asking about accessing the public transit travel time option in the Google Directions API. Up until then I hadn’t used it and assumed it worked the same as the walking and driving time options, but that turned out not to be the case.

The key difference is that you have to supply a time parameter as well as the start and end location. This can either be a departure or an arrival time.

The format required for the time parameter is seconds since midnight on 1/1/1970 (the start of the UNIX epoch), while Excel times are in days since midnight on 1/1/1900. This difference meant it seemed worth writing a function to do the conversions.

So far so good. So what next?

A day of coding an interface for Google’s experimental Time Zone API, that’s what. This is because I can’t assume that everyone’s in the UK – Max is interested in public transport in Singapore for example.

The results are the following functions for your spreadsheeting delight. They’re also fine for VBA for Excel.

=LOCALTIME(Location As String)
Returns the current local time for a given location, including daylight saving

=DST_OFFSET(Location As String)
Returns any daylight saving time offset to the time at a given given location

=UTC_OFFSET(Location As String)
Returns the time difference between UCT and the time at a given given location

=TIMEZONE_ID(Location As String)
Returns the ID of the timezone for a given location

=TIMEZONE(Location As String)
Returns the name of the timezone for a given location

These functions all ought to be pretty stable. They don’t cache results, obviously – since they’re to do with current time. But they do use recursion to keep extending a wait between repeated calls if you have reached Google’s soft limits for the API. These soft limits are where Google throttles the service if you appear to be accessing it too often in a short period of time. The module also includes a fallback to spot if you seem to have hit the hard limit for the day of 25,000 calls. In that case it will return the error message OVER_HARD_QUERY_LIMIT. If that happens then you need to go away for a while and try again the next day (or change your IP address if you’re feeling particularly sneaky).

You can download a workbook here with all of these functions.

This was all something of a distraction from the main purpose of figuring out how to access travel times for public transport. That’s the next task on the horizon in the ever-expanding set of Google API interfaces for Excel that I’m building up here. It’s actually pretty simple, it just needs tidying up so if that’s what you’re waiting for, keep checking back here over the next week or so.

Jamie Bull | jamiebull1@gmail.com

Related Posts

WiGLE is a popular platform which can be used for finding the location of a device using the names of WiFi networks in its vicinity. I’ve written about this before, and wrote some Python code to interact with their API. This API has since been retired and replaced with a new one, as of December […]

Just a quick post to point out a couple of really useful tools.The first is a web-based tool for finding weather files for a location of interest. It’s similar to the Excel EPW finder tool we created a few years back, but much more modern looking. It is however missing a few of the useful […]

Eppy is a really useful library which I’ve written about several times, since before I really had anything to offer in terms of contributing code. Over the past year or so though, I’ve started to contribute back some of the changes and additions I’ve made while using eppy on academic and commercial projects.This post is […]

3 Comments on “Times and time zones in Excel – now with added Google”

  • Steve says:

    Hi Jamie,

    I really enjoy your blog. Thank you for all the help you have been providing. I was wondering if you ever finished this project as I had the exact same question as Max and it doesn’t seem like there ever was a final update on this problem.

    Regards,
    Steve

  • Seth says:

    This is really great. It seems to be off an hour when I lookup my location (Kansas City, MO). I’m not very experienced with VBA, is there some coding changes I need to make before this works right?

Leave a Reply

Your email address will not be published. Required fields are marked *