2 Ways to Calculate Travel Time Between Two Cities in Excel

Today I had to pay for my routine early morning visit to Belson Stadium for a training session, as I devoted myself a freelance footballer. Also, my friends had planned previously to visit the Philadelphia Museum of Art in the afternoon. In contrast, I would not get enough time on my hand. Therefore, I made an ingenious decision to calculate the distance as well as travel time between the two cities in Excel. Though the calculation method is mutable from person to person, on the contrary we were successful while calculating the travel time using Excel. In this article, I will share my story of how I was able to measure both distance and travel time between cities in Excel.

To determine the distance between cities, we plan to elucidate the mathematical method first to calculate the distance and then measure the travel time considering the vehicle’s speed. We are also going to explain pertinent two methods including the use of the COS, SIN, RADIANS, ACOS, IF, INT, MINUTE, and SECOND functions.

How to Calculate Travel Time Between Two Cities in Excel


📁 Download Excel File

To practice please download the Excel file from the link below:


Learn Calculate Travel Time Between Two Cities in Excel with These 2 Methods

Learn 2 approaches to calculate travel time between two cities in Excel. In this article, we consider a dataset named Distance and Time Between Two Cities containing headings of Starting Point and Destination that delineates two different Cities. The dataset has 4 columns along with 8 rows. To construct the methods, we are going to illustrate the use of Mathematical Functions like SIN, COS, RADIANS, and ACOS functions as well as IF, INT, MINUTE, and SECOND functions. We are also determined to expound on the application of VBA Macro. So, let’s get started.

Dataset named Distance and Time Between Two Cities.

method

1. Apply Formula to Calculate Travel Time Between Two Cities in Excel

To Travel from the Belson Stadium, New York City to the Philadelphia Museum of Art, Philadelphia. We need to measure the distance first. As we know that measuring distance is essential to calculate the traveling time. Traveling time is proportional to the distance and inversely proportional to the vehicle speed. The formula to measure the Travel Time is as follows,

Calculation of Travel time.

However, to calculate the Travel time, let us split the method into two steps for better understanding.

Step

Step 1: Calculate Distance Between Two Addresses

To begin the procedure, collect the coordinates (latitude and longitude) for both Belson Stadium and the Philadelphia Museum of Art. By selecting and Right-Clicking on the mouse on the place, You will get the latitude and longitude. Afterward, use the Haversine formula to measure the distance in Miles between two coordinates. The Haversine formula is as follows.

Distance, d = 3959.0 * arccos[(sin(lat1) * sin(lat2)) + cos(lat1) * cos(lat2) * cos(long2 – long1)]

Use 6371 instead of 3959 to express in Kilometers. Excel offers trigonometric functions like SIN, COS, ACOS, and RADIANS functions altogether. Using these congruous trigonometric functions, one can calculate distance easily. Please follow the below steps.

⬇️⬇️ STEPS ⬇️⬇️

  • Initially, select a cell i.e. C10 besides the Distance heading.
  • Then, write down the formula in the C10 cell containing SIN, COS, RADIANS, and ACOS functions.

=(ACOS(COS(RADIANS(90-C7))*COS(RADIANS(90-D7))+SIN(RADIANS(90-C7))*SIN(RADIANS(90-D7))*COS(RADIANS(C8-D8)))*3959)

🔨 Formula Breakdown

👉  Firstly, Calculate the latitude and longitude deducting from 90 degrees.

👉  Secondly, the RADIANS function converts the degree into radian.

👉  Thirdly, the SIN and COS function measures the result.

👉  Fourthly, the inverse Cosine function ACOS converts the calculation into a value and multiplies with 3959 which is the radius of Earth in Miles.

  • Therefore, achieve the Distance of 89.941 Miles between Belson Stadium and the Philadelphia Museum of Art in the C10 cell.

Use of ACOS, RADIANS, COS, and SIN functions to calculate the distance between two cities in Excel.

Step

Step 2: Calculate Travel Time Between Two Cities

As we learned Travel time is dependent on the distance between two points. Dividing the Distance by the vehicle speed, one can calculate the travel time without demur. The use of IF, INT, MINUTE, and SECOND functions will help you to get the travel time. Please follow the further steps to measure the travel time. The syntax of the logical function IF, INT, MINUTE, and SECOND are as follows.

=IF(Logical_test, Value_If_True, Value_If_False)

=INT(Value)

=MINUTE(Serial_Number)

=SECOND(Serial_Number)

INT function considers integer from double values neglecting decimals. MINUTE and SECOND functions both converts the time expressed in Decimals into Minute and Hours.

Please follow the required steps below.

⬇️⬇️ STEPS ⬇️⬇️

  • Again, select a cell i.e. D13 under the Travel Time heading.
  • Secondly, put the following formula in the D13 cell containing the combination of INT, IF, HOUR, MINUTE, and SECOND functions.

=IF(INT($C$10/C13)>0, INT($C$10/C13)&” Hours, “,””) & IF(MINUTE($C$10/C13)>0, MINUTE($C$10/C13) & ” minutes and “,””) & IF(SECOND($C$10/C13)>0, SECOND($C$10/C13) & ” seconds”,””)

🔨 Formula Breakdown

👉  Firstly, $C$10/C13 means, Distance is divided by the speed of Bus and if the result is greater than 0 then it writes the value with hours that are written by an Ampersand(&). Otherwise, the IF function leaves it blank. INT function converts the result into an integer.

👉  Similarly, the MINUTE and SECOND function work to express the decimal in minutes and seconds.

  • Finally, get the Travel Time by Bus in the D13 which results in, 1 Hour, 58 minutes, and 8 seconds. Use the Fill Handle tool, to fill the traveling time automatically in the D13:D16 range.

Use of INT, IF, MINUTE, and SECOND functions to measure the travel time between cities in Excel.

📕 Read More: 9 Tricks to Calculate Time Difference Between AM and PM in Excel

method

2. Utilize Excel VBA Macro to Measure Travel Time between Two Address of Different Cities

Using a VBA code anyone can calculate travel time as well as the distance between two cities within a minute. But applying VBA Macro is not everyone’s cup of coffee. During the use of VBA Macro, we must include various formulas that calculate distance and travel time. But you might have learned that we can’t insert functions and formulas directly while using VBA. To learn the procedure concisely, please Check the required steps below.

⬇️⬇️ STEPS ⬇️⬇️

  • Check first if your Developer option is available or not.
  • Secondly, to enable the Developer option, Right-Click on the mouse at the Top Ribbon and select Customize the Ribbon.

Right-click on the mouse to select Customize the Ribbon.

  • Thirdly, click on the Developer option and hit the OK button.

Enabling the Developer option in the Top Ribbon.

  • Now Developer mode is on.
  • Then, click on the Visual Basic feature.
  • Next, open the dedicated worksheet VBA or the Module from the Insert menu.

Getting the Visual Basic feature from the Developer tab.

  • After that, insert the following VBA code in the dedicated worksheet VBA and hit the OK button.

Code

Sub calculate_travel_time_between_cities()
Dim a As Double
Dim b As Double
Dim c As Double
Dim d As Double
Dim e As Double
Dim p As Double
Dim q As Double
Dim distance As Double
Set wsheet = Application.WorksheetFunction
p = Range("C8").Value
q = Range("D8").Value
a = Cos(wsheet.Radians(90 - Range("C7").Value))
b = Cos(wsheet.Radians(90 - Range("D7").Value))
c = Sin(wsheet.Radians(90 - Range("C7").Value))
d = Sin(wsheet.Radians(90 - Range("D7").Value))
e = Cos(wsheet.Radians(p - q))
distance = (wsheet.Acos(a * b + c * d * e)) * 3959
Range("C10").Value = distance
Dim i As Integer
For i = 1 To 4
Cells(12 + i, 4).Value = Cells(10, 3).Value / Cells(12 + i, 3).Value
Next i
End Sub

🔨 Code Breakdown

👉  Firstly, set a, b, c, d, e, p, q, and distant as double that consider decimals.

👉  Secondly, consider wsheet to run Excel Worksheet’s function.

👉  Thirdly, insert formulas respectively for a, b, c, d, and e.

👉  Fourthly, calculate distance using the formula. Remember, 3959 is multiplied to express in Miles which is the Earth’s Radius; On the other hand, multiply by 6371 to measure in Kilometers.

👉  Then, run a For loop to generate the travel time between two distances.

👉  so, consider i as Integer that dictates the cell reference commuting the row and column number. Cells(12 + i, 4).Value is the result of D13 when i=1.

👉  Thus, For loop proceeds for 4 times till writing the result of the D16 cell.

Use of Excel VBA to compute travel time between two cities.

  • Therefore, we obtain the Travel Time in hours between Belson Stadium and the Philadelphia Museum of Art in the D13:D16 range automatically. In the meantime, the distance between the two cities is also visible in the D10 cell.

Calculated Travel time for each way of traveling.

📕 Read More: 7 Examples to Calculate Duration of Time in Excel


Create Excel Driving Distance Calculator Between Multiple Addresses of Two Cities

My friend Mr. Baten also invited me on his birthday to the Jeffersonville Golf Club. Though I have no intention to visit him as I am a proven dilettante at playing Golf. Moreover, I fear humiliation. If I can convince myself before tomorrow morning, I will change my mind. Before that, I must calculate the distance from the Philadelphia Museum of Art. Please check out the steps below.

⬇️⬇️ STEPS ⬇️⬇️

  • Primarily, Navigate a cell i.e. C10, beside the Distance heading.
  • Then, write down the formula in the C10 cell containing SIN, COS, RADIANS, and ACOS functions.

=(ACOS(COS(RADIANS(90-C7))*COS(RADIANS(90-D7))+SIN(RADIANS(90-C7))*SIN(RADIANS(90-D7))*COS(RADIANS(C8-D8)))*3959)+(ACOS(COS(RADIANS(90-D7))*COS(RADIANS(90-E7))+SIN(RADIANS(90-D7))*SIN(RADIANS(90-E7))*COS(RADIANS(D8-E8)))*3959)

🔨 Formula Breakdown

👉  Firstly, (ACOS(COS(RADIANS(90-C7))*COS(RADIANS(90-D7))+SIN(RADIANS(90-C7))*SIN(RADIANS(90-D7))*COS(RADIANS(C8-D8)))*3959) is the distance from Belson Stadium to Philadelphia Museum of Art.

👉  Additional  (ACOS(COS(RADIANS(90-D7))*COS(RADIANS(90-E7))+SIN(RADIANS(90-D7))*SIN(RADIANS(90-E7))*COS(RADIANS(D8-E8)))*3959) is the distance of Philadelphia Museum of Art to Jeffersonville Golf Club.

👉  ACOS is the Inverse Cosine function.

  • In the end, the Distance of 106.107 Miles is in your hands calculating the distance of three different locations.

Use of ACOS, RADIANS, COS, and SIN functions to calculate the distance between multiple cities in Excel.


📄 Important Notes

🖊️  Enable the Developer menu first to execute VBA code.

🖊️  Accurate coordinates are required to measure distance accurately. Catch the coordinates from Google Maps by Right-Clicking the position of the location.

🖊️  INT function known as the Integer function delineates the accurate result while converting into hrs, mins, and secs format. Nevertheless, miscalculation takes place.

🖊️  ACOS is the Inverse Cosine function.

🖊️  Use 6371 instead of 3959 to express in Kilometers while measuring the distance between two or multiple locations.


📝 Takeaways from This Article

📌  Use of formulas including COS, SIN, RADIANS, and ACOS functions to measure the distance between two different addresses.

📌  Combination of IF, INT, MINUTE, and SECOND functions altogether to convert the decimals of travel time into hrs, mins, and secs format.

📌  VBA Macro to compute both distance and travel time from one place to another.


Conclusion

In this article, we construe 2 laconic approaches to measuring the travel time between two cities. I hope you enjoyed your learning and will be able to compute the distance as well as travel time to multiple locations. Any suggestions including queries are appreciated. Don’t hesitate to leave your thoughts in the comment section. For better understanding and new knowledge, don’t forget to visit www.ExcelDen.com.


Related Articles

(Visited 44 times, 1 visits today)
MD Tanvir Rahman

MD Tanvir Rahman

Hello, I am Tanvir. Graduated from BUET. Had a year-long Experience at Operation of Vitacan Industries Ltd. I found Excel is the most unique software to reduce both time and paper. Now, I am an Excel enthusiast, love to play with data and publish content.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo