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

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.

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

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.

Download Practice Workbook

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

Calculate Time Difference.xlsm

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.

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

Get FREE Advanced Excel Exercises with Solutions!

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

How to Calculate Time Difference in Excel VBA: 2 Methods

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

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

Examples to Calculate Time Difference Between Two Times with Excel VBA

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

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

Things to Remember

Conclusion

Related Articles

Leave a reply Cancel reply