VBA: How to Calculate Days Between Two Dates

You can use the following basic syntax in VBA to calculate the number of days between two dates:

Sub DaysBetweenDates()

    Dim i As Integer

    For i = 2 To 7
        Range("C" & i) = DateDiff("D", Range("A" & i), Range("B" & i))
    Next i
    
End Sub

This particular example will calculate the number of days between the dates in the corresponding cells in the ranges A2:A7 and B2:B7 and return the results in the range C2:C7.

Note that the “D” argument in the DateDiff function specifies that we would like to calculate the date difference in terms of days.

Refer to the VBA documentation page for a complete list of units you can use for the date difference.

The following example shows how to use this syntax in practice.

Example: Calculate Days Between Dates in VBA

Suppose we have the following two columns with dates in Excel:

Suppose we would like to calculate the number of dates between the start and end date in each row and output the results in column C.

We can create the following macro to do so:

Sub DaysBetweenDates()

    Dim i As Integer

    For i = 2 To 7
        Range("C" & i) = DateDiff("D", Range("A" & i), Range("B" & i))
    Next i
    
End Sub