How to Calculate Time Difference in VBA (With Examples)

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