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

VBA Code to Calculate the Time Difference in Excel VBA


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

VBA Code to Calculate the Time Difference in Excel VBA

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

VBA Code to Calculate the Time Difference in Excel VBA

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.

Output to Calculate the Time Difference in Excel VBA

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

Output to Calculate the Time Difference in Excel VBA

⧪ 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

VBA Code to Calculate the Time Difference in Excel VBA

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

VBA Code to Calculate the Time Difference in Excel VBA

⧭ 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

VBA Code to Calculate the Time Difference in Excel VBA

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

VBA Code to Calculate the Time Difference in Excel VBA

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.

Output to Calculate the Time Difference in Excel VBA

⧪ 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

VBA Code to Calculate the Time Difference in Excel VBA

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.

Output to Calculate the Time Difference in Excel VBA

⧪ 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

VBA Code to Calculate the Time Difference in Excel VBA

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

VBA Code to Calculate the Time Difference in Excel VBA

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

Data Set to Calculate the Time Difference in Excel VBA

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

VBA Code to Calculate the Time Difference in Excel VBA

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

Entering Function to Calculate the Time Difference in Excel VBA

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.

Rifat Hassan

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo