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

When we run this macro, we receive the following output:

VBA calculate time difference

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

Leave a Reply

Your email address will not be published. Required fields are marked *