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
Read More: How to Add and Subtract Time in Excel
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.
Read more: How to Add and Subtract Dates in Excel
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 UserDefined 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:
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:
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  DateTime in Excel  Learn Excel
Get FREE Advanced Excel Exercises with Solutions!