I had a comment from Gabriel on the Google Distance post, asking if it could be adapted. After a bit of back-and-forth by email with Google Translate as an intermediary (Gabriel is Brazilian and my live-in translator/interpreter/girlfriend is in Brazil too so she couldn’t help), I figured out that what Gabriel wanted was to return the time taken rather than the distance.

This sounded like it might be a useful addition – you’d be able to work out things like average speed travelled. And it also turned out not to be too difficult, needing just a few changes to the function.

The first one is to change "//leg/distance/value" to "//leg/duration/value". That was easy enough to find out by referring to the XML schema for the Google Maps Directions API.

The other main thing to handle is that the Google API returns the duration in seconds, which needs converting into a more useful format. I decided to give two options with this – to return it as decimal hours where 1 1/2 hours is represented as 1.5 hours, and to return it in Excel’s date format where 1 1/2 hours is shown as 01:30:00. I chose the second as default.

The syntax for using the function is as follows:
=G_TIME(Origin, Destination) to return the time taken in Excel time format (you’ll need to format the cell to make it display correctly).
=G_TIME(Origin, Destination, "Decimal") to return the time taken as a decimal.

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 […]

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 […]

Leave a Reply

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