MD Tanvir Rahman
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.
📁 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.
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,
However, to calculate the Travel time, let us split the method into two steps for better understanding.
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.
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.
📕 Read More: 9 Tricks to Calculate Time Difference Between AM and PM in Excel
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.
- Thirdly, click on the Developer option and hit the OK button.
- 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.
- 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.
- 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.
📕 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.
📄 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
- 4 Easy Ways to Calculate Percentage of Time in Excel
- How to Calculate Average Response Time in Excel
- 4 ways to Create Excel Formula for Overtime Over 8 Hours
- How to Calculate Overtime and Double Time with Formula in Excel
- Calculate Hours Between Two Dates and Times in Excel Excluding Weekends
- 6 Approaches to Subtract Time in Excel and Show Negative
- How to Calculate Turnaround Time in Excel Excluding Weekends
