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

Get FREE Advanced Excel Exercises with Solutions!

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

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 in the comment section below.

## Related Articles

<< Go Back to Subtract Time | Calculate Time

## What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Rifat Hassan

Rifat Hassan, BSc, Electrical and Electronic Engineering, Bangladesh University of Engineering and Technology, has worked with the ExcelDemy project for almost 2 years. Within these 2 years, he has written over 250 articles. He has also conducted a few Boot Camp sessions on effective coding, especially Visual Basic for Applications (VBA). Currently, he is working as a Software Developer to develop and deploy additional add-ins to enhance the customers with a more sophisticated experience with Microsoft Office Suits,... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF