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

42 Comments on “A Google Maps journey time function for Excel”

  • Joerg Froehlich says:

    Hello Jamie,

    sorry for my bad english, coming from Germany 😉

    First: thanks for your great work and your examples!

    But I think there is a mistake in the “G_TIME”-code:


    If Format = "Decimal" Then ' Return as a decimal - 30 mins as 0.5 hrs
    G_TIME = timeNode.Text / 3600 ' Seconds in an hour
    Else 'Return in Excel's 00:00:00 date format - 30 mins as 00:30:00
    G_TIME = 1 + timeNode.Text / 86400 ' Seconds in a day
    End If

    I think, that after the ‘ELSE’-line it must be:

    G_TIME = timeNode.Text / 86400 ' Seconds in a day

    For what is th one day plus? I found that by calculating with times; try formatting a result-cell with “[hh]:mm”.

    Greetings from Germany
    Joerg

  • Ringo says:

    Hi,

    First of all, this is very useful, and easy to use

    At the moment, the G_TIme functions gives journey time by car. I wonder if it can be modified to give journey time by transit and on foot as well??

    Thanks,
    Ringo

    • Jamie Bull says:

      Hi Ringo,

      Yes, it’s very simple to adapt. All you need to do is add &"mode=walking" or &"mode=transit" to the request. So the line would be:

      For walking

      For public transit

      I haven’t actually tested the public transit code but the walking one definitely worked for me. So as long as your area is covered by Google for public transport it should work fine.

      Hope that helps.

  • Max says:

    Great code, but please help with the transit part. I used the code, and it shows the drive time. It would be great to get the transit time, please. Appreciate your help.

    • Jamie Bull says:

      I haven’t tested this but if you set the mode to transit (see the answer to Ringo’s comment) then the journey time returned should be the transit time.

      • Max says:

        Thank you, Jamie. When I tested it out based on the code above -Ringos comment, I was getting transit time by vehicle, and not time by public transit. I was also looking at this for Singapore where the data is available on Google.

        Is there a way to get the public transit time (train, bus, metro, etc.) based on the first result as you would get when searching for directions using public transit on google maps?

        Transit time by car works like a charm using your code, and now if we can get public transit to work, that would be awesome !

        Look forward to your guidance and insight…thank you, again.

        • Jamie Bull says:

          Having looked into this a bit further I see that the public transit mode is a bit different to the others. If you read the API documentation you can see that you need to send either an “arriving by” or “leaving at” time parameter in the form of the number of seconds since midnight GMT on 1/1/1970. And that’s still before you get to the point of finding the transit time from the returned XML. I’ll have to let this one sit a little while before I have time to write a module for it. I’m afraid.

          • Max says:

            Appreciate your thinking this one through and offering a glimmer of hope – patience remains a virtue, and I’ll be awaiting your module…thanks again.

          • Zach says:

            Hi Jamie,

            Any update on the public transportation or (alternatively) a link to the API documentation?

            Thanks,
            Zach

          • jihane says:

            Hello,

            Just in case there are some other people looking for triptime with public transportation, the link to the api documentation is: https://developers.google.com/maps/documentation/directions/intro
            and it shows the same code as the one written by jamie Bull with no departure or arrivall time; they are both optional and the default value is now for the departure time.

            thanks Jamie Bull for your code, it helped me a lot.

            Best regards.

  • John says:

    first of all thank you,

    I wonder if it´s possible to get the travel time in current traffic from google.

    Thank you again,

    John

    • Steve says:

      I too have greatly appreciated you work here. I would also like to see you can get the duration_in_traffic value, which is travel time in current traffic. I have not been able to get it to work.

      • Alex says:

        Based on the Google Maps API documentation: “This feature is only available for Google Maps API for Work customers.”

  • Pedro says:

    Thank you very much for your help with this.

  • Kashif says:

    First of all thanks for posting this stuff online. It is awesome. For some reason I can’t match the results of the api to the actual results from google website.

    For example I test the distance between two US addresses
    Start: 18 Sieber Court, Bergenfield NJ 07621
    End: 6 Gwendolyn Drive, Trenton NJ 08638

    G_DISTANCE: 108
    If I go on google maps website the result is about 78 miles.

    • Jamie Bull says:

      Hi Kashif,

      There are two things going on here. First, the result you’re getting is in kilometres. Second, there are two routes suggested by Google Maps when I try those locations. The journey via US Route 1 is about 69 miles, which is close to the 108 km which you found from the G_DISTANCE function.

      I hope that makes sense.

  • Chimbo77 says:

    I am a finance person, that has some VB skills that were acquired many years ago. My Company does a poor job of estimating freight costs. Thinking that I could make a spreadsheet that could do this reasonably well using Google and a little statistics I came up with a bare bones model. The model is generally within 5%; of the actual bill for ground transportation in Europe, North and South America. Using G_Duration or G_Distance I was able to estimate the driving distance; however when the transport is by ship or barge then my spreadsheet returns an error message. I have also tried the walking, flight and driving, but without success. I am expecting there is a G_XXX, but I have not found it. I do not know where else to look and being on hold with Google I found you (by the way, it is now an hour and forty minutes I have been on hold).

    Matt

    • Jamie Bull says:

      Hi Matt,

      Thanks for getting touch. It’s always good to hear that someone’s getting some good use out of them. The limitations are frustrating but I’m afraid Google don’t have APIs for shipping, rail or air freight otherwise you can be sure I would have added them to the tool. I do have a method in mind for developing a shipping distance calculator but as yet no customer to make the development time worthwhile. If that’s something you or your company might be interested in just let me know.

      Jamie

  • Fabrizio says:

    I was using G_time in excel with an IF before to find times between airports. Im having a hard time because after copying the formula, the destination cells are not updated. I had to go one by one, with F2. Now not even that is helping I get 00:00:00 every where. What can I do to update (recalculate) the values properly?

  • Fabrizio says:

    thank you for your help. yesterday was working with a few destinations. Now with 300 it doesnt work,
    I found your other post with the sleep function. I declare it and include it in the code it keeps running ( no need to debug) but I still get 0’s

    • Jamie Bull says:

      I wonder if you’ve gone over the API limits. Are you looking at a 300 x 300 matrix or just 300 start/end pairs? The matrix I would expect to fail as it’s just too much for the API limits but the 300 pairs should be ok.

  • Fabrizio says:

    I think I hit he limit. Is a matrix of 100X100 but only 323 couples. the problem is that I didi them several times, between yesterday and today because of the 0’s and the pasting problem, plus the trying the sleep 200.
    What can I do now. Should I simply wait for tomorrow?

    • Jamie Bull says:

      Either wait till tomorrow, or alternatively if you have access to a VPN or another network you could try using that to get a different IP address.

  • Mohsin Rahim says:

    This code is very helpful but I have noticed that every time I run it for the same two points, it gives a different value. And the value differs significantly each time. Why is that?

    • Jamie Bull says:

      My first guess was that it’s to do with traffic conditions, but unless you’ve specified that then it won’t be. If you’re looking at public transport then the times can be expected to vary. Otherwise I have no idea. Obviously Google are returning different results but without seeing the response I couldn’t say what’s different.

  • Tony Rogers says:

    This is so helpful, I use the GDistance all the time. But I couldn’t get it to work myself, but someone kindly posted a spreadsheet with all the code already in it, which was great.
    I’m now trying to add the GTime stuff, but again I can’t get it to work, either by pasting in the new code, or by modifying the GDistance code.
    Is there any chance someone could post a copy of a spreadsheet with the VBA code already embeded? Much appreciated.

    • Jamie Bull says:

      Glad you’re finding G_Distance useful. Have you added a reference to Microsoft XML, v6.0 in the VBE editor? What error are you getting?

  • Danny says:

    Hi, I have been using your code and modified it as it is really useful for me to get travel times & duration of travels. I am quoting jobs for my company using this tool and finally tried to use “Flights” MODE. This however fails to work when retrieving duration of travel. It still gives me the time it takes to get to destination with “Driving”. Is there a way around it or is “flights” the wrong code to use in the mode of transport.

    Kind Regards,

    Danny

    • Jamie Bull says:

      Hi Danny, Glad you’re finding it useful. I’m afraid there’s no duration calculation for flights though, only distance.

  • AM says:

    Hi Jamie,

    First thank you for developing such an useful tool. When I used the G_DISTANCE worksheet, and enter distance in one column and different rows, it is showing me some distances as wrong a compared to google maps. Could you please tell me the reason for that.

    Thanks

    • Jamie Bull says:

      It could be a number of things. If the address given isn’t precise then the starting point my not be quite where you intend it to be. Google may be serving an alternative route which takes the same time but is a different distance. The Maps route may be different due to accounting for traffic. Without knowing the specifics it’s hard to tell though.

  • rashida says:

    This is a great tool! Is it possible to modify this to get average travel time between 2 points at certain times and days? My use case is this. I am looking to buy a house and would like to see what is the average driving time from potential areas at peak traffic times!

    Thanks

    • Jamie Bull says:

      There is a parameter to choose a specific time of day, but I believe it requires a commercial licence to use. As it stands the time given is the current estimated time. That’s probably not quite what you want, though you can at least make sure to check it at the times you’d expect to be travelling.

  • Kim says:

    Perfect job you are doing. Thanks.
    I suppose, the normal time is calculated going say 130 on Highways
    But if I go in a truck, in tourist-bus or in my car with a trailer, I will have a max speed of say 100 km per hour. Is there any way to calculate the time?

    Thanks for your help.

    • Jamie Bull says:

      An interesting comment, but I’m afraid not. Not just using the current Google API at least. If it’s worth it to someone, it might be possible to work it out from each leg of the journey returned by the Directions API, but it would be a fair bit of work and I imagine would require setting a max speed for your vehicle for each type of road.

  • Kim says:

    Okay, so I start my car and bring my stop-watch 🙂
    Thanks for your answer!
    BR Kim

  • Keith says:

    Will I need to get my own Google API key to make this work?

  • Jacob says:

    This is awesome! Although I can’t get it to work for transit, It won’t accept more than one parameter in the function.. Do you have a spreadsheet with a working code for transit mode?

  • Adrian says:

    Hi, first, this is great!
    Question;
    Do you know how to make the driving time more accurate so it predicts it as at a particular time of day when traffic is congested and if i wanted to get to a destination at a particular time what time would i have to leave?
    Many thanks
    A

    • Jamie Bull says:

      Hi Adrian,

      Sorry for the delay. Google does provide this option but not on the free API. You’d need to purchase a commercial API key from them but if you had that, it’s relatively simple.

      Jamie

Leave a Reply

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