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&", "&C21`

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