You can use the following basic syntax in VBA to count the number of dates that are greater than a specific date:
Sub CountifGreaterDate()
Range("D2") = WorksheetFunction.CountIf(Range("A2:A10"), ">" & Range("C2"))
End Sub
This particular example counts the number of dates in the range A2:A10 that are greater than the date in cell C2.
The result is then assigned to cell D2.
The following example shows how to use this syntax in practice.
Example: Countif Greater than Date Using VBA
Suppose we have the following list of dates in Excel and we’d like to know the number of dates that are greater than 4/25/2023:
We can create the following macro to do so:
Sub CountifGreaterDate()
Range("D2") = WorksheetFunction.CountIf(Range("A2:A10"), ">" & Range("C2"))
End Sub
When we run this macro, we receive the following output:
Cell D2 tells us that there are 6 dates in the range A2:A10 that are greater than 4/25/2023.
Note that we can change the value in cell C2 and run the macro again to count the number of dates greater than a different specific date.
For example, suppose we change the date in cell C2 to 10/1/2023 and run the macro again:
We can see that there are 3 dates greater than 10/1/2023.
Note: If you want to count the number of dates greater than or equal to a specific date, simply change the “>” in the CountIf function to “>=” instead.
Additional Resources
The following tutorials explain how to perform other common tasks in VBA:
How to Sort by Date in VBA
How to Sum If Between Two Dates in VBA
How to Add Days to Date in VBA
