Search

# Calculate travel time and distance between two addresses using Excel + Maps API

Share

Ever wanted to calculate distance using Excel  – between two locations (physical addresses)?

If we know the addresses, we can go to either Google Maps or Bing Maps and type them out to find the distance and travel time. But what if you are building some model (or calculator) and want to find out the distance, travel time, address points (latitude, longitude) and may be even distance matrix (given two sets of points, all distances between them)? We can use the public APIs from Bing maps or Google Maps to get the answer to our spreadsheet.

What you need:

• Free maps API from either Google Maps or Bing Maps
• Excel 2013 or above (we will be using WEBSERVICE() and FILTERXML() functions in Excel)

How to get the API Key from Google Maps or Bing Maps:

The API key process is somewhat technical and can be confusing. Plus for Google Maps API, you need to provide your credit card details (according to Google, you will not be billed automatically though). I made a small video explaining the process. Watch it below (or on our YouTube channel).

## Using Excel to calculate distance & travel time between two points – Bing Maps API

As the process for getting Bing Maps API key is easy, let’s assume that is what you have.

Let’s say you have the api key in a cell named bingmaps.key

In this demo, we focus on calculating distance & travel time between one set of points, but you can use the ideas to calculate distance matrix for a range of points. For example, you can calculate travel time between all your warehouses and customer locations easily.

Start by creating a range of cells to capture origin & destination addresses. For Bing maps API, we need address to be broken in to below pieces.

### Step 1: Fetch Latitude and Longitude for the addresses

Before calculating the distance, we need to know where on earth our addresses are. So we will use point lookup API to convert address to geolocation (lat&long). To do this, we call

Notice all \$ symbols? Use SUBSTITUTE to replace them with actual location values.

When you call this URL using WEBSERVICE(), you will get an XML output (as our output parameter is o=xml, if you omit this, you will get json).

Sample output for this looks like below:

From this XML, we need to extract the LAT & LONG values highlighted in blue. We can use FILTERXML() to do that.

Let’s say the output of WEBSERVICE is in cell C21.

We can use FILTERXML() like this:

=FILTERXML(C21,”//Latitude[1]”)

=FILTERXML(C21,”//Longitude[1]”)

This will give us both lat & long values.

How does FILTERXML() work? It takes the XML value in C21 and finds the first Latitude tag (hence [1]) anywhere (hence //)

You can use FILTERXML to test the status code for the response or other interesting bits too.

### Step 2: Calculate distance between two geolocations

Once we have lat & long values for both origin and destination, we can call distance lookup API to calculate distance, travel time values.

The distance lookup URL is:

https://dev.virtualearth.net/REST/v1/Routes/DistanceMatrix?origins=\$1&destinations=\$2&travelMode=\$3&o=xml&key=bingmaps.key

For example, the distance lookup URL for above addresses is:

https://dev.virtualearth.net/REST/v1/Routes/DistanceMatrix?origins=-41.22292,174.80164&destinations=-41.27868,174.77506&travelMode=driving&o=xml&key=\$k

The output for this is an XML that looks like:

<?xml version=”1.0″ encoding=”utf-8″?><Response xmlns:xsd=”http://www.w3.org/2001/XMLSchema” xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance” xmlns=”http://schemas.microsoft.com/search/local/ws/rest/v1″><Copyright>Copyright © 2018 Microsoft and its suppliers. All rights reserved. This API cannot be accessed and the content and any results may not be used, reproduced or transmitted in any manner without express written permission from Microsoft Corporation.</Copyright><BrandLogoUri>http://dev.virtualearth.net/Branding/logo_powered_by.png</BrandLogoUri><StatusCode>200</StatusCode><StatusDescription>OK</StatusDescription><AuthenticationResultCode>ValidCredentials</AuthenticationResultCode><TraceId>_REMOVED_</TraceId><ResourceSets><ResourceSet><EstimatedTotal>1</EstimatedTotal><Resources><Resource xsi:type=”DistanceMatrix”><ErrorMessage>Request accepted.</ErrorMessage><Origins><Coordinate><Latitude>-41.22292</Latitude><Longitude>174.80164</Longitude></Coordinate></Origins><Destinations><Coordinate><Latitude>-41.27868</Latitude><Longitude>174.77506</Longitude></Coordinate></Destinations><Results><Distance><DepartureTime xsi:nil=”true” /><OriginIndex>0</OriginIndex><DestinationIndex>0</DestinationIndex><TravelDistance>8.96955555555556</TravelDistance><TravelDuration>7.29166666666667</TravelDuration><TotalWalkDuration>0</TotalWalkDuration></Distance></Results></Resource></Resources></ResourceSet></ResourceSets></Response>

Again, we can use FILTERXML() to extract the relevant bits (=FILTERXML(C32,”//TravelDistance[1]”) and =FILTERXML(C32,”//TravelDuration[1]”))

The default output values are in KM for distance and minutes for duration. You can change this  to miles, hours etc. too by using extra parameters in the lookup URL. Please read the Bing maps developer documentation for more.

## Distance & travel time in Excel – Google Maps API

Let’s say your Google Maps API key is in a cell named gmaps.key

This API is really easy to use compared to Bing maps (as we need to make just one call).

The request URL is:

For example, let’s lookup the travel time and distance between Microsoft & APPLE offices.

The sample URL is:

The response is XML (if you want json, then replace xml with json) like below:

<?xml version=””1.0″” encoding=””UTF-8″”?>
<DistanceMatrixResponse>
<status>OK</status>
<row>
<element>
<status>OK</status>
<duration>
<value>47736</value>
<text>13 hours 16 mins</text>
</duration>
<distance>
<value>1379709</value>
<text>1,380 km</text>
</distance>
</element>
</row>
</DistanceMatrixResponse>

We can FILTERXML this response to extract the important bits like this:

=FILTERXML(C15,”//distance[1]/text”)

=FILTERXML(C15,”//duration[1]/text”)

More examples of WEBSERVICE():

### Made something cool with WEBSERVICE()?

Did you make something cool and fun using WEBSERVICE() and FILTERXML()? Please share the ideas and tips in comments section.

### Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

### Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Excel School made me great at work.
5/5

– Brenda

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

### Sales Analysis Dashboards with Power BI – 30+ Alternatives

Do you need inspiration for your upcoming Power BI sales dashboard? Well, I got you covered. In this page, let me present 33 alternatives for Sales Analytics Dashboards with Power BI.

## Related Tips

Excel Howtos

Excel Howtos

Excel Howtos

Excel Howtos

Excel Howtos

Excel Howtos

### 79 Responses to “Calculate travel time and distance between two addresses using Excel + Maps API”

1. José Lôbo says:

Get API Key
To use the Maps JavaScript API, you must register your app project on the Google Cloud Platform Console and get a Google API key which you can add to your app.

2. Amit Jalan says:

Thank You so much... It's Really Helpful. We have HTTP Api for Sending SMS and Getting status of the same. With the help of WebService Function, It's become very easy to update the status.

3. Trevithick says:

Back in 2012 I attempted to create a Great Circle Calculator with Excel based on examples at ExcelAutomate.

I was able to calculate bearings, distances, and so forth, but never could get them to plot properly.

I would love to see you take your disctance and time calculating capability and tweak it to produce Great Circle plots.

• Hui... says:

@Trevithick

Plotting circles is quite easy using named formula

If you ask the question in the Chandoo.org Forums,
https://chandoo.org/forum/
also attach a sample file with a drawing of what your after

4. FrankT says:

Is there a way to make this stuff culture aware?

Currently the nice file provided is useless if your decimal separator is a comma.

Using Bing I tried adding "&culture=de" but that didn't help.

5. Karl Mueller says:

How do I change output to miles?

• Chris Hanson says:

For the Microsoft API insert "&distanceUnit=mi" into the Distance Lookup URL

• Chandoo says:

Good suggestion Chris. I believe Google Maps API too has a parameter to set units for the output values. You can also use Excel's own CONVERT function to do this - =CONVERT(90,"km","mi")

• Tim Emerick says:

Thought I would add that you can add units=imperial to the url to get miles instead of km.

6. Kanyalal Bhatia says:

Dear Chandoo,
This is Awesome, could u please Tweak a little to add the function such as :
1. drop down to calculate time value in any \$ range 10 to 20.
3. Drop down Cost per mile using range \$1 to 3
4. To select one way or round trip cost.
5. To cal calculate weight of package more than 5 lbs @ rate \$ 0.10.
This will be a snap for u to tweak, I currently calculate manually using Excel.
Thanks for all the help u provide to excel community
kanu
2156774149.

7. Jonathan says:

I've generated a Bing key and inserted it at the right place but the spreadsheet doesn't seem to work for me:Most fields show " #NAME? ". Any idea what the problem might be?

Cheers 🙂

• Chandoo says:

@Jonathan... Thanks for the question. Which version of Excel are you using? WEBSERVICE() is a new function and you need at least Excel 2013.

• Jonathan says:

@Chandoo.. I'm using Excel for Mac Version 16.17 180909.

8. Hans says:

Thanks a lot for this guide, it really helped!

Since it is much easier to get an API Key for Bing, i am using this alternative. Is there way to calculate the distance and time between two coordinate avoiding tolls? (i used "&avoid=tolls&" in the URL, but it is not working).

• pk says:

even i tried using &avoid=tolls but it doesnt work

9. Donald Claessens says:

Hello Chandoo

In the example you calculate the time as a decimal number. How can ik convert this to the timeformat h:mm:ss

• Hui... says:

@Donald

Select the cell/s
Right click and select format Cells or Press Ctrl+1
Select the Number Format tab
Select the Custom Format Tab
In the Custom Format Dialog enter h:mm:ss or [h]:mm:ss
apply

The [] mean that if the number is say 27 hrs it will display as 27 not as 3

10. Fitch says:

I use LibreOffice Calc, and having no luck in getting the travel distance with a filterxml.
=FILTERXML(C32,”//TravelDistance[1]”)
brings up #N/A.

I tried:
FILTERXML(C32,"//ResourceSets/ResourceSet/Resources/Resource/Results/Distance/TravelDistance")
Again, I just get #N/A

Could you explain how it's meant to be structured?
Thanks

• Fitch says:

Got the answer from the LibreOffice forum.
Apparently, you have to take the XML's "default namespace" into account
FILTERXML(S3,"//*[local-name() = 'TravelDistance']")
That worked for me.

11. Anne says:

Hello Chandoo

I'm using the Bing Maps API. I've a question about the results of the formulas of coördinates, km and minutes. The calculationsheet gives a traveldistance and travelduration of -1. Also the coördinates aren't right. For example, I've got 532191 instead of 53.2191. Can you explain how this is possible?

Thank you!

12. Tim King says:

Chandoo,

Thank-you for your article, which has been a great help.

It is frustrating that these solutions do not work on Excel for Mac, but I have Parallels and Excel for Windows running.

Also to say that to get distance out, I used

=filterXML(cell,"//row/element/distance/value") for metres, and then converted to miles.

Best regards,

Tim.

13. Varun Raj Singh says:

Hi Chandoo,

Thanks for this. I used your google maps sheet to re-create one with columns based on my need. I procured the API key, but when I use it (either on your sheet or the one I created), the status reads REQUEST_DENIED.

This is possibly nothing to do with the sheet itself, but any suggestions on how I can rectify this?

14. Tanner says:

I get a value error not sure why I have an API key and when I review my console it show the errors so it reaching to the API Any ideas how to fix?

• Rukhsana says:

Go to credentials and request new key then copy and paste, it worked for me

15. Zaki says:

Hi Chandoo,

I just obtained a bing maps api key. Do you know how address distances I can calculate under the the free tier?

16. Axelle says:

Hi,

I got the google API as you explained.
When I copy this into the sheet, it doesn't work.
status = REQUEST_DENIED

Am I doing something wrong?

17. Varun Raj Singh says:

Hi Chandoo!

When I change travel mode from Driving to Transit, I do not see a change in either Distance or Travel Time.

Is there something else I need to do?

18. Allen says:

Hello Chandoo

Can you help me code up a module for the bing map api so that it will be easer to get the distance of 2 address if you have a them in a list. i use to use the google api however its not free anymore.

19. Jeanine Holtmann says:

Thank you so much for this!!

20. Aswin Iruppayil Raj says:

Hi Chandu, thanks for this - its very useful.

When I send this sheet to others its not working. The sheet has my API key, are there any restrictions on my API key if its been used by anyone other than me?

The cell returns a value error.

• Rukhsana Tanwir says:

Same problem with my distance sheet

21. Wayne Garside says:

Hi Chandu

Is there a way to choose a specific time or day of travel? Or even get an average travel time across a range (e.g. between 08:00-09:00 on a weekday in February)?

Wayne

22. Marc Stelzer says:

Wondering if there's a way to get "Depart at" time in the formulas so traffic at, say, 7AM could be considered

23. Rukhsana Tanwur says:

Hi Chando,
I tried your distance calculator but I can only use it on my computer, I need to share it interactive so other people can change addresses to calculate distances, I convert it to google sheets but google sheets does not have full funcionality, tried One drive it also did not work, please let me know how can I share it without liising functionality

• Chandoo says:

Hi Rukhsana...

When you share the files online, your users will also use the same key. If you are using paid keys, then shared files can increase the uses rapidly and you will be billed accordingly. I think Excel online based sharing should work as long as you are ok with the above.

24. Gilson says:

Hello. The function Web Service () does not work correctly. When I execute the link everything is ok, but the cells Response and Distance can not show the results.

• Chandoo says:

@Gilson, can you test the webservice call by pasting the URL in browser? Does it give a result? If not, you may need to find the correct URL + parameters.

• Gilson says:

Yes, the URL is ok when testing in browser. Only the result showed in the ceel of Excel is #VALUE

• Josh says:

@Gibson, were you able to solve this? I am having the same issue and from what I can tell, it's not a problem with the url. I am unable to use WEBSERVICE for any url.

25. Camilla says:

I have an problem in Bing maps sheet.

Distance is -1,00 km and Travel Time is also -1,00 mins how can I get the correct values?

• Florian R. says:

This is because of your Settings to "Point" and "Comma" in Excel (or your operating system).

In Excel, go to "File" --> "Options" --> "Advanced" (don't know the english title, sorry) --> deactivate option "use delimiter from operating system", change decimal to ".", thausend to "," --> "OK"

afterwards it will work.

26. Mwanangu says:

Great Post Chandoo!
Do you have a distance calculator for multiple addresses?

Hello
I tried to use the Bing template to calculate distances and time of travel exactly as it was shown in this page but for some reason, it is not working !!!
I have my own key in Bing.

Do you have an idea if I am missing something?

Thank you

28. James says:

Did they move the urls? I get #VALUE! no matter what I do...

29. Haley says:

I'm wondering what the travel time is calculating - is this current traffic, average traffic, or no traffic?

30. macutan says:

I am having the same "-1.00" distance and travel time issue, it seems to work for "Driving" Mode but shows -1.00 when I change the mode to Walking (which is what I am trying to use this for). any thoughts?

31. Majush says:

Chandoo ,
1) I created a Distance Matrix with all the locations you want to visit and the intersection give distance and
2)another table where intersection gives the time taken .
3)Then using excel Solver ,
3a)using this info, I created an objective function to minimize the distance travelled for roundtrip
3b)calculated the exact time the vehicle reaches each stop and standard wait time of 5 mins.
3c) Applied constraints that some stops wants the visit time in between say 3pm- 4 pm or 2pm - 6 pm .. like that
3d) Solve it and it gave the best TSP solution .
Succesfully ran upto 20 stops( may take 2 mins to run) , beyond that it will take too much time .

32. Wojo says:

Chandoo -
I'm using the Bing maps calculator and both Travel modes (walking & driving) are calculating but when selecting the Travel Mode as 'transit', it only calculates as '0'. I am wondering if the URL query is missing some required values. I need to add 'arrival time' as a parameter which is a required parameter when using 'transit' but not sure how to proceed with adding the arrival time field into excel and editing the required URL parameters. Thoughts?

• Wojo says:

Sorry - I am NOT using 'transit' but rather 'driving' as the Travel Mode. I need to add 'arrive by time' to the Lookup URLs for the 'driving' query but not sure how to proceed with adding the 'arrive by time' field into excel and editing/adding the syntax into the Lookup URLs. Can you assist?

33. Ipshita says:

Hi Chandoo,

Thanks a lot for the blog. This has been very helpful. I am using the Bing maps api. However, i wanted to know if there is any way to find the shortest distance between two coordinates in case of multiple routes.

Thanks

34. Dileep says:

Hi Chandoo, thanks for the video. How can I specify the departure time (past) in the excel? I wanted to compare travel time between two points for two different time of the day. Thanks

35. Emma Littlewood says:

This is the best set of instructions I've found - thank you so much.
I don't know which restrictions to apply to my API key, as I'm using it from Excel not an online app etc.
Does anyone know how I can let another team member use my API key on our shared drive - it doesn't let him use it, just says #VALUE.

THANKS!

• Chandoo says:

Hi Emma... Thank you. Can you check and confirm your colleague has access to Google API website and using Excel 2013 or above (which is needed for this to work)?

36. Nikolas Jones says:

Hi Chandoo

Great instructions, easy to use.

The only problem I had was I kept getting API not authorised error message when following the link.

I discovered on google, you have to enable the API for use with Distance Matrix before it works.

I have a Mac : ( which means web service doesn't work. So even after I figured out the API issue, I will never be able to see the beautiful results displayed in excel.

Thanks anyway

Have a great day.
Nik

37. Sandeep kumar says:

Hi sir can u please tell me how can we calculate the journey speed between two places with in the city using the google map APIs

38. Marie says:

Hi, Thank you for these instructions. I have been using your model for some time, but suddenly, the results for different modes of transport (transit/walking/driving) are the same. It seems whichever mode of transport is selected, the result is the driving travel time. I checked Microsoft documentation as well, but I could not find why it does not work as it should. Any idea, please?

39. Neil says:

Great script. I was able to use the bing maps API. How do you add a waypoint to the calculation? Can this script be modified to do a list of pickup and dropoff points?

40. Wessel Lourens says:

Hi Chandoo, I have been using your script for some time and it calculated distances and times correctly. Today, however it returned -1.00 distance. I have used my lats and longs in your sheet as well and get the same result. What is causing this?

I have the same problem. I have a value "-1" for Distance and Travel Time. What does it mean?

The statuscodevalue = 200. What does it mean?

I have a valid api key.

I ame working with "Office 365"

41. Steve says:

Hi, Chandoo:

Great website and samples to help us to learn and leap forward.Great job and appreciate it.

I recently download your Distance calculator template and it does work for the first day, and then stops working. I have checked every detail and even re-downloaded your template by just input my Bingmaps Key. It seems like that the API stopped working. Can you help to verify it?

Thank you so much

42. Masoud says:

Hello Chandoo,

Thanks for providing your excel files.

I would like to know if there is any way to calculate travel duration in past time in Bing API. For example, for 2020-03-01.

Best,
Masoud

43. Mike says:

Mouth literally agape at how awesome this is you put this together. Literally thought I had 0 chance at finding a way to do this through Excel - though i did luck out we had existing API keys! Amazing and thank you so much!

44. Martin says:

Hi,

I am trying to use your template but only get #VALUE" errors in the results fields.

Pasting the url directly into the browser shows me the correct xml answer

Any suggestion?

Thx

• Remy says:

Hi,

I also get the #VALUE! error too with the Bing Maps API Key. In the browser, I get the correct XML answer.

45. Gray Wolf says:

This is amazing. Thank you @Chandoo.
Is there a simple way to add a 3rd point on the route? Instead of start and end. I was looking for a way to have Start, waypoint, end.
Any assistance would be greatly appreciated.
Thank you and keep up the fantastic work.
Gray

46. Jason says:

This is great, thank you. For the Google sheet, is there a way to get flight times in addition to the pre-defined choices of "driving, walking, bicycling & transit"? I added "flight" (since that seems to be a valid Google API designation) to the data validation dropdown list, but it returns the same info as driving. Also, can you think of a way to enter multiple locations in the spreadsheet and then calculate distances from one location to all of them? I am trying to calculate which one of my employees is closest to a client location that I enter. Thanks.

47. Feta says:

How can i have the output of Distance to be numeral only so without units. As i will use this number in another formula. I have used the Google APIs option you've shown.

Thanks you.

• Chandoo says:

You can use substitute function to remove any letters like KM or Mi from the output and then apply VALUE() function to convert the thing to numbers.

48. Javos says:

Hello,
this file was working perfectly... until some weeks ago.
I'm using the google maps solution. Clicking on the URL, I got following message:
The provided API key is invalid.

I tried to create a new key, but with the same result. Someone has a solution for this? Or is this still working for others?

thanks

49. Natalia says:

Great calculator!
Is it possible somehow to calculate the distance for "flight" mode ?

50. Nigel says:

Hi Chandoo,

I need to use the exact model that you provided here (https://chandoo.org/wp/distance-between-places-excel-maps-api/), but must also have a stopover (waypoint) between the origin and destination.

How can I please adjust the formulas to fit in a stopover in the middle?

I've edited your formula like so:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(gmaps.distance.url;"\$1";SUBSTITUTE('Matrix Interface'!B16&", "&'Matrix Interface'!C16;" ";"+"));"\$2";SUBSTITUTE('Matrix Interface'!D16&", "&'Matrix Interface'!E16;" ";"+"));"\$4";SUBSTITUTE('Matrix Interface'!D18&", "&'Matrix Interface'!E18;" ";"+"));"\$3";gmaps.travel.mode);"\$k";gmaps.key)

This is the resultant URL:

However, the waypoint doesn't come up in the response:

"

OK

4852 Rothrist, Switzerland

4624 Härkingen, Switzerland

OK

839

14 mins

9981

10.0 km

"

Hope to hear back from you.

Best regards,

Nigel

51. Kody says:

I used this template last week and it worked perfectly. I went to put the finishing formatting touches on it, and I'm getting #VALUE for time and distance. I tried creating a new API key to see if that was the issue and that didn't fix it. I went back to your original template and I'm having the same issue. Do you know what it could be?

52. Hi Chandoo,
Concerning the Download distance calculator template, i have the following questions and i wish you will answer them in this order:

1. Where exactly am I supposed to insert my bing maps API key?
2. What is the earliest version of microsoft excel that this formula works in?
3. Does it generate the distance matrix on its own or do i have to do anything else to generate the entire distance matrix?
4. If I have to generate the driving distance matrix myself, how do i do that?

Thank you helping.

53. Ludger Boecken says:

Hi there,

for some reason the file works great iwith US zip codes, unless they start with a "0", like NJ or New England. The the calculator spits out zeros for distance and driving time, no matter what the zip codes are.

I formatted the zip codes a s zip codes in Excel, not as text. This way they should be recognized as number and still show the "0" at the beginning.

Any idea what the problem is?

Ludger

### Get FREE Excel & Power-BI Newsletter

One email per week with Excel and Power BI goodness. Join 100,000+ others and get it free.