While working with times with VBA in Excel, we often have to calculate the time difference between two times. In this article, Iâ€™ll show you how you can calculate the difference between two times with VBA in Excel. Youâ€™ll learn to calculate the time difference with proper examples and illustrations involving Macro and User-Defined Functions.

Calculate Time Difference in Excel VBA (Quick View)

```
Sub Time_Difference_by_Direct_Substitution()
Time1 = CDate("6:03:59 AM")
Time2 = CDate("7:05:10 AM")
Total_Seconds = (Time2 - Time1) * 24 * 3600
MsgBox Total_Seconds
Total_Minutes = Int(Total_Seconds / 60)
MsgBox Total_Minutes
Total_Hours = Int(Total_Seconds / 3600)
MsgBox Total_Hours
Hours = Int(Total_Seconds / 3600)
Minutes = Int((Total_Seconds Mod 3600) / 60)
Seconds = Int((Total_Seconds Mod 3600) Mod 60)
Total_Time = Str(Hours) + ":" + Str(Minutes) + ":" + Str(Seconds)
MsgBox Total_Time
End Sub
```

**How to Calculate Time Difference in Excel VBA: 2 Methods**

So, without further delay, letâ€™s move to our main discussion. You can calculate the number of days between two dates in 2 ways with VBA.

**Method 1: Calculate Time Difference Between Two Times by Direct Subtraction**

First of all, you can calculate the **difference between two times** by direct substitution.

Letâ€™s have two dates, **6:03:59 AM** and **7:05:10** **AM**.

First, weâ€™ll use the **CDate **function of VBA to convert the strings to times.

```
Time1 = CDate("6:03:59 AM")
Time2 = CDate("7:05:10 AM")
```

Image 1.

Next, weâ€™ll calculate the difference between the two times by direct substitution.

**â§ª Case 1: Calculating Total Seconds**

To calculate the difference between two times in total seconds, use:

```
Total_Seconds = (Time2 - Time1) * 24 * 3600
MsgBox Total_Seconds
```

Run the code, and itâ€™ll display the time between the two times in full seconds.

**â§ª Case 2: Calculating Total Minutes**

To calculate the difference between two times in total minutes, use:

```
Total_Minutes = Int(Total_Seconds / 60)
MsgBox Total_Minutes
```

Run the code ( remove the line **MsgBox Total_Seconds **if you donâ€™t want to see the total seconds)Â and itâ€™ll display the time between the two times in full minutes.

**â§ª Case 3: Calculating Total Hours**

To calculate the difference between two times in total hours, use:

```
Total_Hours = Int(Total_Seconds / 3600)
MsgBox Total_Hours
```

Run the code (remove the lines **MsgBox Total_Seconds** and **MsgBox Total_Minutes** if you donâ€™t want to see them) and itâ€™ll display the time between the two times in full hours.

**â§ª Case 4: Calculating Hours, Minutes, and Seconds Together**

To calculate the time difference in hours, minutes, and seconds together, use:

```
Hours = Int(Total_Seconds / 3600)
Minutes = Int((Total_Seconds Mod 3600) / 60)
Seconds = Int((Total_Seconds Mod 3600) Mod 60)
Total_Time = Str(Hours) + ":" + Str(Minutes) + ":" + Str(Seconds)
MsgBox Total_Time
```

Run the code (remove the lines **MsgBox Total_Seconds**, **MsgBox Total_Minutes, **and** MsgBox Total_Hours** if you donâ€™t want to see them) and youâ€™ll get the difference between the two times in hours, minutes, and seconds together.

Therefore, the complete VBA code will be:

**â§ VBA Code:**

```
Sub Time_Difference_by_Direct_Substitution()
Time1 = CDate("6:03:59 AM")
Time2 = CDate("7:05:10 AM")
Total_Seconds = (Time2 - Time1) * 24 * 3600
MsgBox Total_Seconds
Total_Minutes = Int(Total_Seconds / 60)
MsgBox Total_Minutes
Total_Hours = Int(Total_Seconds / 3600)
MsgBox Total_Hours
Hours = Int(Total_Seconds / 3600)
Minutes = Int((Total_Seconds Mod 3600) / 60)
Seconds = Int((Total_Seconds Mod 3600) Mod 60)
Total_Time = Str(Hours) + ":" + Str(Minutes) + ":" + Str(Seconds)
MsgBox Total_Time
End Sub
```

**â§ Output:**

Run the code, and itâ€™ll display the total seconds, total minutes, total hours, and the hours, minutes, and seconds together one by one.

**Read More: **How to Add and Subtract Time in Excel

**Method 2: Calculate Time Difference Between Two Dates by DateDdiff Function of VBA**

You can use the **DateDfiff **function of VBA to calculate the difference between two times in Excel VBA.

Here also, weâ€™ll use the **CDate **function to convert the strings to dates.

**â§ª Case 1: Calculating Total Seconds**

To calculate the difference between two times in total seconds, use:

```
Total_Seconds = DateDiff("s", Time1, Time2)
MsgBox Total_Seconds
```

Run the code, and itâ€™ll display the difference between the two times in full seconds.

**â§ª Case 2: Calculating Total Minutes**

To calculate the difference between two times in total minutes, use:

```
Total_Minutes = DateDiff("n", Time1, Time2)
MsgBox Total_Minutes
```

Run the code ( remove the line **MsgBox Total_Seconds **if you donâ€™t want to see the total seconds)Â and itâ€™ll display the time between the two times in full minutes.

**â§ª Case 3: Calculating Total Hours**

To calculate the difference between two times in total hours, use:

```
Total_Hours = DateDiff("h", Time1, Time2)
MsgBox Total_Hours
```

Run the code (remove the lines **MsgBox Total_Seconds** and **MsgBox Total_Minutes** if you donâ€™t want to see them) and itâ€™ll display the time between the two times in full hours.

**â§ª Case 4: Calculating Hours, Minutes, and Seconds Together**

To calculate the time difference in hours, minutes, and seconds together, use:

```
Hours = Int(Total_Seconds / 3600)
Minutes = Int((Total_Seconds Mod 3600) / 60)
Seconds = Int((Total_Seconds Mod 3600) Mod 60)
Total_Time = Str(Hours) + ":" + Str(Minutes) + ":" + Str(Seconds)
MsgBox Total_Time
```

Run the code (remove the lines **MsgBox Total_Seconds**, **MsgBox Total_Minutes, **and** MsgBox Total_Hours** if you donâ€™t want to see them) and youâ€™ll get the difference between the two times in hours, minutes, and seconds together.

Therefore, the complete **VBA** code will be:

**â§ VBA Code:**

```
Sub Time_Difference_by_DateDiff_Function()
Time1 = CDate("6:03:59 AM")
Time2 = CDate("7:05:10 AM")
Total_Seconds = DateDiff("s", Time1, Time2)
MsgBox Total_Seconds
Total_Minutes = DateDiff("n", Time1, Time2)
MsgBox Total_Minutes
Total_Hours = DateDiff("h", Time1, Time2)
MsgBox Total_Hours
Hours = Int(Total_Seconds / 3600)
Minutes = Int((Total_Seconds Mod 3600) / 60)
Seconds = Int((Total_Seconds Mod 3600) Mod 60)
Total_Time = Str(Hours) + ":" + Str(Minutes) + ":" + Str(Seconds)
MsgBox Total_Time
End Sub
```

**â§ Output:**

Run the code, and itâ€™ll display the total seconds, total minutes, total hours, and the hours, minutes, and seconds together one by one.

**Read more: **How to Add and Subtract Dates in Excel

**Examples to Calculate Time Difference Between Two Times with Excel VBA**

Weâ€™ve learned to calculate the difference between two times using Excel **VBA**. Now, letâ€™s explore a few examples of this.

**Example 1: Developing a Macro to Calculate Difference Between Two Columns of Times with Excel VBA**

Here weâ€™ve got a data set with the **Names, Attending Times,** and **Leaving Times** of some employees of a company on a particular day.

Now weâ€™ll develop a **Macro** to calculate the working time of each of the employees.

That is, weâ€™ve to calculate the differences between the two times of the columns **C4:C13** and **D4:D13**.

**â§ VBA Code:**

```
Sub Time_Difference()
Set Attending_Times = Range("C4:C13")
Set Leaving_Times = Range("D4:D13")
Set Working_Times = Range("E4:E13")
For i = 1 To Attending_Times.Rows.Count
Time1 = Attending_Times.Cells(i, 1)
Time2 = Leaving_Times.Cells(i, 1)
Total_Seconds = (Time2 - Time1) * 24 * 3600
Hours = Int(Total_Seconds / 3600)
Minutes = Int((Total_Seconds Mod 3600) / 60)
Seconds = Int((Total_Seconds Mod 3600) Mod 60)
Total_Time = Str(Hours) + ":" + Str(Minutes) + ":" + Str(Seconds)
Working_Times.Cells(i, 1) = Total_Time
Next i
End Sub
```

**â§ Output:**

Run this code. Itâ€™ll calculate the working time of each employee in the range **E4:E13**.

**Example 2: Creating a User-Defined Function to Calculate Time Difference Between Two Times with Excel VBA**

Now weâ€™ll create a **User-Defined function** thatâ€™ll take two times as the arguments, and return the difference between them.

The **VBA** code will be:

**â§ VBA Code:**

```
Function TimeDifference(Time1, Time2)
Total_Seconds = (Time2 - Time1) * 24 * 3600
Hours = Int(Total_Seconds / 3600)
Minutes = Int((Total_Seconds Mod 3600) / 60)
Seconds = Int((Total_Seconds Mod 3600) Mod 60)
Total_Time = Str(Hours) + ":" + Str(Minutes) + ":" + Str(Seconds)
TimeDifference = Total_Time
End Function
```

**â§ Output:**

Select the cell **E4** from the worksheet and enter this formula:

**=TimeDifference(C4,D4)**

Itâ€™ll return the difference of times between the two times in cells **C4** and **D4**.

Now you can drag the **Fill Handle** to copy the formula to the rest of the cells.

**Things to Remember**

Weâ€™ve used the **Datediff **function of VBA in method 2 of our process. But thereâ€™s almost a similar function in Excel called **the DATEDIF function**. But it can only calculate the difference between two dates, not two times.

**Download Practice Workbook**

Download this practice workbook to exercise while you are reading this article.

**Conclusion**

So, these are the ways to use calculate the difference between two times with VBA in Excel. Hope the examples made everything clear for you. Do you have any questions? Feel free to ask us in the comment section below.

## Related Articles

- How to Add and Subtract Columns in Excel
- How to Add and Subtract Multiple Cells in Excel
- How to Add or Subtract Based on Cell Value in Excel
- Adding and Subtracting in Excel in One Formula
- How to Add and Subtract in One Cell in Excel

**<< Go Back to Subtract Time | Calculate Time****Â | Date-Time in Excel | Learn Excel**