Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Calculate Travel Time Between Two Cities in Excel

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.


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.

calculate travel time between two cities in excel

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

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

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

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

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

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

  • Finally, to calculate the travel time, use the formula below.
[email protected](C28,"//TravelDuration[1]")
  • To display the outcome, press the Enter key.

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

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

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

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

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

Read More: How to Calculate the Duration of Time in Excel (7 Methods)


Similar Readings


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.

Calculate Travel Time Between Two Cities Using Google Maps API

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

calculate travel time between two cities in excel

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

Sabrina Ayon

Sabrina Ayon

Hi there! This is Sabrina Ayon. I'm really excited to welcome you to my profile. Currently, I'm working in SOFTEKO as a Team Leader. I'm a graduate in BSc in Computer Science and Engineering from United International University. I love working with computers and solving problems. I’ve always been interested in research and development. Here I post articles related to Microsoft Excel. Hoped this may help you. Thank you.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo