How to Calculate Travel Time Between Two Cities in Excel (2 Ways)

Microsoft Excel may determine the separation between two specific cities or locations on the planet. It is essential to be able to determine the distance between any two locations on a map. It gives you the ability to calculate the time required for driving, walking, bicycling, and using public transportation. Excel, however, makes it simple to accomplish that. In this article, we will demonstrate several ways to calculate travel time between two cities in Excel.

How to Calculate Travel Time Between Two Cities in Excel: 2 Effective Ways

Excel may be used for many different things. And it appears that we can accomplish everything we want in Excel. Knowing the addresses allows us to use Google Maps or Bing Maps to calculate the travel time. For this, we need the APIs, identifying and authenticating an application or user requires the use of an application programming interface (API) key. The API key procedure involves certain technical steps and might be challenging.

1. Use Bing Maps API Key to Calculate Travel Time Between Two Cities

We can get Bing maps API keys for free. Let’s follow the procedures to calculate travel time between two cities in Excel starting with getting the key.

STEPS:

Firstly, we will need the key. For this, go to Bing Maps.
Secondly, click on the sign-in button and sign in with your Microsoft Account.

Thirdly, create a Bing account, and the Bing maps | Dev Center page will appear.

Further, click on the menu option (three lines).
Then, go to the My account drop-down menu and select My Keys.

Thus, you will be able to see a page like the screenshot below. From there, click on the Show key.
This will display your Bing maps key. Copy the key by pressing Ctrl + C.

At this point, we need to go to the Excel workbook and paste the key by entering the keyboard shortcut Ctrl + V.

Sequentially, we must first know the locations of our destinations on the planet in order to calculate the distance. In order to convert an address to geolocation, we will utilize the point lookup API (latitude & longitude).
So, we put the Point Lookup URL in cell C6. And, insert the Distance Lookup URL in cell C7.

We are going to compute the travel time between origin and destination. For this, we enter all the necessary information.

Eventually, to get the URLs to get the response from the web service we are using the SUBSTITUTE function. This function replaces text by matching. So, in cell C16 we insert the 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)

Further, press Enter.
Similarly in cell D16 enter the formula 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 and 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 are going to use the WEBSERVICE function. So, we select cell C18 and insert the formula into that cell.

=WEBSERVICE(C16)

Further, press Enter to get the response.

Likewise, in the previous step, to get the destination response, we select cell D18 and insert the formula.

=WEBSERVICE(D16)

Press the Enter button from your keyboard.

Now, we acquire the status of the origin. For this, insert the following formula into cell C19. We will use the FILTERXML function, which enables us to retrieve the data from an XML file.

=@FILTERXML(C18,"//StatusCode[1]")

Pressing the Enter key will show the result.

Similarly, for acquiring the destination status we will enter the formula in cell D19.

=@FILTERXML(D18,"//StatusCode[1]")

Furthermore, press the Enter key.

Next, we need the latitude of the origin, so select cell C20 and type the formula there.

=@FILTERXML(C18,"//Latitude[1]")

To display the outcome, press Enter.

Afterward, we need to find the latitude of the destination. So, we select the cell D20 and put the formula.

=@FILTERXML(D18,"//Latitude[1]")

Pressing the Enter key will show the outcome.

We will get the longitude of the origin and the destination by inserting the following formula sequentially into cells C21 and D21.

To get origin longitude,

=@FILTERXML(C18,"//Longitude[1]")

For destination longitude,

=@FILTERXML(D18,"//Longitude[1]")

You must press the Enter key every time after inserting the formula into the cells.

At this point, we will obtain the location of both; the origin and destination. For this, we will college the cells of latitude and longitude.

For origin,

=C20&", "&C21

Considering destination,

=D20&", "&D21

After entering the formula into the fields, you must always press the Enter key.

Now, we will create a travel mode drop-down list. For this, go to the Data tab > Data validation. In our case, there are three options; driving, walking, and transit.

Again, we need the URL, for this select cell and use the formula.

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(distance.url,"$1",C23),"$2",D23),"$3",travel.mode),"$k",bingmaps.key)

Press Enter to display the URL.

For a response from the server, we are again using the formula with the WEBSERVICE function. But this is for getting the travel time.

=WEBSERVICE(C27)

Pressing Enter will display the response in that cell.

Now, to find the status, we will use the following formula.

=@FILTERXML(C28,"//StatusCode[1]")

To view the outcome, press the Enter key.

After that, we have to calculate the distance. So, we 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 the Enter key.

This is an overview of calculating the travel time when the travel mode is driving.

If you change the travel mode, this will automatically change the travel time.

2. Calculate Travel Time Between Two Cities Using Google Maps API

To begin with, we need the Google Maps API. For this, we need to go to visit Google Maps. Then, create an account and get the key. If you don’t have a Google Maps API account, I will suggest using the Bing Maps API. Let’s start by obtaining the key and work our way through the Excel steps to get the travel time between two cities.

STEPS:

Firstly, get the key from the Google Maps API.
Secondly, put the request URL.

Further, we calculate the travel time between origin and destination. For this, we enter all the necessary information.
We will now develop a drop-down menu for the trip modes.
Go to the Data tab > Data validation to do this. We have four options: driving, walking, bicycling, and public transportation.

Finally, we are utilizing the SUBSTITUTE function to acquire the URLs to receive the answer from the web service. So, we 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 the Enter key 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.

We once more use the formula with the WEBSERVICE function to request a response from the server. However, this is for calculating travel time.

=WEBSERVICE(C11)

Pressing Enter will return the response details of the server.
As we request the server, so status we set is REQUEST_DEFINED.

Likewise the previous method, again we are going to use the FILTERXML function to calculate the distance and the travel time between two cities.

For distance,

=@FILTERXML(C12,"//distance[1]/text")

For travel time,

=@FILTERXML(C12,"//duration[1]/text")

Every time you enter a formula into a cell, you must press the Enter key.

This 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. You won’t be charged automatically, per Google, nevertheless.
Copy the formula of the SUBSTITUTE function to get the URL, just modify the formula as per your cell reference.

Download Practice Workbook

You can download the workbook and practice with them.

Calculate Travel Time Between Two Cities.xlsx

Conclusion

The above ways will assist you to Calculate Travel Time Between Two Cities in Excel. Hope this will help you! Please let us know in the comment section if you have any questions, suggestions, or feedback.

<< Go Back to Calculate Total Time | Calculate Time | Date-Time in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!