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.

**Read More:** Excel Calculate Hours Between Two Times After Midnight

**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.

**Read More:** Calculate Elapsed Time Between Two Dates in Excel

**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.

**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.

## 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