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.

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

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.

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

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

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