Rifat Hassan
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
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
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.
Read More: Excel formula to find difference between two numbers
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.
Similar Readings
- Calculate Positive or Negative Difference Between Two Numbers in Excel
- How to Calculate Absolute Difference between Two Numbers in Excel
- Pivot Table: Percentage Difference between Two Columns
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:
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. And don’t forget to visit our site ExcelDemy for more posts and updates.
