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