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

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

The number of dates between each start date and end date is shown in column C.

Additional Resources

The following tutorials explain how to perform other common tasks in VBA:

How to Compare Dates in VBA
How to Filter a Column Using VBA
How to Sort Values Alphabetically in VBA

Leave a Reply

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