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

**Table of Contents**hide

**Download Practice Workbook**

You can download the workbook and practice with them.

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

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 must 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, also 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.

`[email protected](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**.

`[email protected](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.

`[email protected](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.

`[email protected](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,

`[email protected](C18,"//Longitude[1]")`

For destination longitude,

`[email protected](D18,"//Longitude[1]")`

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

- At this point, we will obtain the location of both; 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
**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.

`[email protected](C28,"//StatusCode[1]")`

- To view the outcome, press the
**Enter**key.

- After that, we have to calculate the distance. So, we use the formula.

`[email protected](C28,"//TravelDistance[1]")`

- Press
**Enter**.

- Finally, to calculate the travel time, use the formula below.

`[email protected](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:** **How to Calculate the Duration of Time in Excel (7 Methods)**

**Similar Readings**

**How to Calculate Hours and Minutes in Excel (7 Handy Ways)****Calculate Hours Between Two Dates and Times in Excel Excluding Weekends****How to Calculate Cycle Time in Excel (7 Examples)****Excel Calculate Hours between Two Times after Midnight (3 Methods)****How to Subtract Minutes from Time in Excel (7 Methods)**

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

`[email protected](C12,"//distance[1]/text")`

For travel time,

`[email protected](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:** **How to Calculate Average Response Time in Excel (4 Methods)**

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

**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. Or you can glance at our other articles in the **ExcelDemy.com** blog!

## Related Articles

**Calculate Time Difference Between AM and PM in Excel****How to Calculate Percentage of Time in Excel (4 Suitable Examples)****Use Time Format in Excel VBA (Macro, UDF, and UserForm)****How to Subtract Date and Time in Excel (6 Easy Ways)****Calculate Elapsed Time Between Two Dates in Excel (5 Methods)****How to Calculate Time in Excel (16 Possible Ways)**