Table of Contents
Comparing dates in VBA is done by using DateDiff, which returns the difference between two dates in a given unit of time, such as days, months, or years. This function can be used to compare two dates and determine which one is greater than or less than the other. The result of the comparison can be used to trigger certain actions, such as running a code or displaying a message. It can also be used to compare dates and evaluate conditions based on the result.
You can use the following basic syntax in VBA to compare two dates:
Sub CompareDates()
Dim i As Integer
For i = 2 To 5
If CDate(Range("A" & i)) < CDate(Range("B" & i)) Then
Result = "First Date is Earlier"
Else
If CDate(Range("A" & i)) > CDate(Range("B" & i)) Then
Result = "First Date is Later"
Else
Result = "Dates Are Equal"
End If
End If
Range("C" & i) = Result
Next i
End Sub
This particular example will compare the dates in the corresponding cells in the ranges A2:A5 and B2:B5 and return the result of the date comparisons in the range C2:C5.
Note: The CDate function converts the value in a given cell to a date.
The following example shows how to use this syntax in practice.
Example: Compare Dates in VBA
Suppose we have the following two columns with dates in Excel:
Suppose we would like to compare the dates in each corresponding row and output the results of the date comparison in column C.
We can create the following macro to do so:
Sub CompareDates()
Dim i As Integer
For i = 2 To 5
If CDate(Range("A" & i)) < CDate(Range("B" & i)) Then
Result = "First Date is Earlier"
Else
If CDate(Range("A" & i)) > CDate(Range("B" & i)) Then
Result = "First Date is Later"
Else
Result = "Dates Are Equal"
End If
End If
Range("C" & i) = Result
Next i
End Sub
When we run this macro, we receive the following output:
The results of the date comparisons are now shown in column C.