You can use the following basic syntax in VBA to calculate the difference between two times:
Sub FindTimeDifference()
Dim i As Integer
For i = 2 To 7
'calculate time difference in days
Range("C" & i) = Range("B" & i) - Range("A" & i)
'calculate time difference in hours
Range("D" & i) = (Range("B" & i) - Range("A" & i)) * 24
'calculate time difference in minutes
Range("E" & i) = (Range("B" & i) - Range("A" & i)) * 24 * 60
'calculate time difference in seconds
Range("F" & i) = (Range("B" & i) - Range("A" & i)) * 24 * 60 * 60
Next i
End Sub
This particular macro will calculate the difference between the corresponding times in the ranges A2:A7 and B2:B7 and return the following results:
- C2:C7 will contain the time difference in days
- D2:D7 will contain the time difference in hours
- E2:E7 will contain the time difference in minutes
- F2:F7 will contain the time difference in seconds
The following example shows how to use this syntax in practice.
Example: Calculate Time Difference in VBA
Suppose we have the following two columns of start and end times in Excel:
We can create the following macro to calculate the time difference between each start and end time and display the results in columns C through F:
Sub FindTimeDifference()
Dim i As Integer
For i = 2 To 7
'calculate time difference in days
Range("C" & i) = Range("B" & i) - Range("A" & i)
'calculate time difference in hours
Range("D" & i) = (Range("B" & i) - Range("A" & i)) * 24
'calculate time difference in minutes
Range("E" & i) = (Range("B" & i) - Range("A" & i)) * 24 * 60
'calculate time difference in seconds
Range("F" & i) = (Range("B" & i) - Range("A" & i)) * 24 * 60 * 60
Next i
End Sub
When we run this macro, we receive the following output:
Columns C through F display the time difference between the start and end times in various units.
Additional Resources
The following tutorials explain how to perform other common tasks in VBA:
How to Compare Dates in VBA
How to Calculate Days Between Two Dates in VBA
How to Convert String to Date in VBA