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

**Download Practice Workbook**

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

**2 Methods to Calculate Time Difference in Excel VBA (Step by Step Analysis)**

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.

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

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

**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. And donâ€™t forget to visit our site **ExcelDemy** for more posts and updates.