How to Calculate Time Difference in Excel VBA (2 Methods)

Excel VBA Code to Calculate Time Difference (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

Method 1 – Calculate Time Difference Between Two Times by Direct Subtraction

We have two times: 6:03:59 AM and 7:05:10 AM.
We’ll use the CDate function in VBA to convert these strings to actual time values:
```
Time1 = CDate("6:03:59 AM")
Time2 = CDate("7:05:10 AM")
```
Let’s calculate the difference between these two times in various units:
- Case 1: Total Seconds

To find the difference in total seconds, enter:

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

Run the code, and it’ll display the difference between the two times in total seconds.

- Case 2: Total Minutes

To calculate the difference in total minutes, enter:

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: Total Hours

To determine the difference in total hours, enter:

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 it) and it’ll display the time between the two times in full hours.

- Case 4: Hours, Minutes, and Seconds Together

To express the difference in hours, minutes, and seconds, insert:

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 it) and you’ll get the difference between the two times in hours, minutes, and seconds together.

The complete VBA code will be:

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

Method 2 – Calculate Time Difference Between Two Dates using the DateDdiff Function in VBA

You can also use the DateDiff function in VBA to find the difference between two times.

Here’s how:

Convert the strings representing the times to actual dates using the CDate function:
```
Time1 = CDate("6:03:59 AM")
Time2 = CDate("7:05:10 AM")
```
Calculate the difference in various units:

- Case 1: Total Seconds

To find the difference in total seconds, enter:

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: Total Minutes

To calculate the difference in total minutes, insert:

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 difference between the two times in full minutes.

- Case 3: Total Hours

To determine the difference in total hours, copy and paste:

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 it) and it’ll display the difference between the two times in full hours.

- Case 4: Hours, Minutes, and Seconds Together

To express the difference in hours, minutes, and seconds, enter:

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 it) and you’ll get the difference between the two times in hours, minutes, and seconds together.

The complete VBA code will be:

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

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

Let’s explore the examples of calculating time differences between two times using Excel VBA.

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

Suppose we have a dataset containing employee names, attending times, and leaving times for a particular day.

Our goal is to calculate the working time for each employee. Specifically, we want to find the differences between the times in columns C4:C13 and D4:D13.

Here’s the VBA code to achieve this:

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

When you run this code, it will calculate the working time for each employee and populate the results in the range E4:E13.

Example 2 – Creating a User-Defined Function to Calculate Time Differences Between Two Times with Excel VBA

Now let’s create a custom function that takes two times as arguments and returns their difference.

Here’s the VBA code for the function:

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

To use this function, select cell E4 in your worksheet and enter the following formula:

=TimeDifference(C4,D4)

This will return the time difference between the values in cells C4 and D4.

You can then drag the Fill Handle to copy the formula to other cells.

Things to Remember

Remember that while we used the DateDiff function in Method 2, Excel also has a similar function called DATEDIF. However, DATEDIF only calculates differences between dates, not times.

Download Practice Workbook

You can download the practice workbook from here:

Calculate Time Difference.xlsm