If we have the addresses of two specific cities or locations on the planet, we can calculate the travel time between them in Excel by making use of a third party geolocation service like Google Maps or Bing Maps. In this article, we’ll demonstrate how to use both of them by accessing their APIs (Application Programming Interface) using an API key.
Method 1 – Using Bing Maps API Key
We can get Bing maps API keys for free and use their API to calculate travel time between two cities.
Steps:
- To get the key, go to Bing Maps.
- Click on the sign-in button and sign in with your Microsoft Account.
- Sign up for an account first if necessary.
- Enter your Bing account, and the Bing maps | Dev Center page will appear.
- Click on the menu option (three lines).
- Go to the My account drop-down menu and select My Keys.
- The My Keys page opens, like in the image below.
- Click on Show key.
This will display your Bing maps key.
- Copy the key by pressing Ctrl + C.
- Go to the Excel workbook and paste the key in cell C4.
We must first know the locations of our destinations in order to calculate the distance between them. In order to convert an address to geolocation, we will utilize the point lookup API (latitude & longitude).
- Put the Point Lookup URL in cell C6.
- Insert the Distance Lookup URL in cell C7.
- Enter the necessary address information for the origin and destination locations as in the image below.
To get the URLs to get the response from the web service, we’ll use the SUBSTITUTE function. This function replaces text by matching.
- In cell C16 enter the following formula for the origin:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(point.url,"$1",C$14),"$2",C$13),"$3",C$12),"$4",C$11),"$5",C$10),"$k",bingmaps.key)- Press Enter.
- Similarly, in cell D16 enter the formula below for the destination:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(point.url,"$1",D$14),"$2",D$13),"$3",D$12),"$4",D$11),"$5",D$10),"$k",bingmaps.key)- Press Enter.
How Do the Formulas Work?
Here, we get all the necessary information from column C and column D to match the text with bingmaps key, then replace the text with bingmaps key.
To get the response from the web server for finding the latitude and longitude of the origin and the destination, we’ll use the WEBSERVICE function.
- In cell C18 enter the following formula:
=WEBSERVICE(C16)- Press Enter.
- Like in the previous step, to get the destination response, in cell D18 enter the following formula:
=WEBSERVICE(D16)- Press Enter.
Now, we acquire the status of the origin. We will use the FILTERXML function, which enables us to retrieve the data from an XML file.
- Enter the following formula into cell C19:
=@FILTERXML(C18,"//StatusCode[1]")- Press Enter to show the result.
- Similarly, for acquiring the destination status, enter the following formula in cell D19:
=@FILTERXML(D18,"//StatusCode[1]")- Press Enter.
Next, we need the latitude of the origin.
- In cell C20 enter the following formula:
=@FILTERXML(C18,"//Latitude[1]")- To display the outcome, press Enter.
- To find the latitude of the destination, in cell D20 enter the following formula:
=@FILTERXML(D18,"//Latitude[1]")- Press Enter to show the outcome.
- To find the longitude of the origin and the destination, enter the following formulas sequentially into cells C21 and D21:
For origin longitude:
=@FILTERXML(C18,"//Longitude[1]")For destination longitude:
=@FILTERXML(D18,"//Longitude[1]")- Press Enter after entering each formula.
Now we can collate the location of both the origin and destination cells from their latitudes and longitudes.
For origin:
=C20&", "&C21For destination:
=D20&", "&D21- Press Enter after entering each formula.
Now, we will create a travel mode drop-down list.
- Go to the Data tab > Data validation.
There are three options to consider when calculating the travel time between our locations; driving, walking, and transit.
- To return the URL for this web server call, select cell C27 and enter this formula:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(distance.url,"$1",C23),"$2",D23),"$3",travel.mode),"$k",bingmaps.key)- Press Enter to display the URL.
- To return the travel times, we’ll again use a formula with the WEBSERVICE function:
=WEBSERVICE(C27)- Press Enter to display the response.
- To find the status, use the following formula:
=@FILTERXML(C28,"//StatusCode[1]")- To view the outcome, press Enter.
- Next we have to calculate the distance. Use the formula:
=@FILTERXML(C28,"//TravelDistance[1]")- Press Enter.
- Finally, to calculate the travel time, use the formula below:
=@FILTERXML(C28,"//TravelDuration[1]")- To display the outcome, press Enter.
This is an overview of the travel time calculation when the travel mode is driving.
- If you change the travel mode in the URL, the travel time will automatically change.
Read More: Excel Calculate Hours Between Two Times After Midnight
Method 2 – Using Google Maps API
As with Bing Maps, in this method we’ll need to get an API key for Google Maps API from Google Maps. If you don’t have one, create an account and log into Google Maps.
Steps
- Get the key from the Google Maps API.
- Enter the request URLÂ as in the image below:
Now, as before, we develop a drop-down menu for the trip modes.
- Go to the Data tab > Data validation.
We have four options: driving, walking, bicycling, and public transportation.
We use the SUBSTITUTE function to acquire the URLs to receive the answer from the web service.
- Enter the origin’s formula in cell C11:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(gmaps.distance.url,"$1",SUBSTITUTE(C7," ","+")),"$2",SUBSTITUTE(C8," ","+")),"$3",gmaps.travel.mode),"$k",gmaps.key)- Press Enter to get the URL.
How Does the Formula Work?
Here, we obtain all the data required to match the text gmaps key and swap it out for the gmaps key.
- Enter the following formula to calculate the travel time:
=WEBSERVICE(C11)- Press Enter to return the response details of the server.
As we request the server, we set the status to REQUEST_DENIED.
As in the previous method, we’ll use the FILTERXML function to calculate the distance and the travel time between two cities.
For the distance:
=@FILTERXML(C12,"//distance[1]/text")For the travel time:
=@FILTERXML(C12,"//duration[1]/text")- Press Enter after entering each formula.
Here is an explanation of how to determine journey time when driving.
The travel time will be adjusted automatically if the mode of transportation is modified.
Things to Keep in Mind
- You must enter your credit card information for the Google Maps API. However, you won’t be charged automatically by Google.
- Copy the formula of the SUBSTITUTE function to get the URL, just modify the formula for your cell reference.
Download Practice Workbook
Related Articles
- How to Calculate Hours Between Two Times in Excel
- How to Calculate Hours and Minutes in Excel
- How to Calculate Hours from Date and Time in Excel
- How to Calculate Time Difference Between AM and PM in Excel
- How to Calculate Difference Between Two Dates and Times in Excel
- Calculate Hours Between Two Dates and Times in Excel Excluding Weekends
<< Go Back to Calculate Total Time | Calculate Time | Date-Time in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
