Forum Discussion
Comparing dates in VBA
You can use code similar to
If Date1 > Date2 Then
MsgBox "Date1 is later then Date2"
ElseIf Date1 = Date2 Then
MsgBox "Date1 is equal to Date2"
Else
MsgBox "Date1 is earlier than Date2"
End If
HansVogelaar Thanks for this. However my problem seems to be with formatting the dates rather than how to report the comparison. I have this code:
Sub datetest()
iday = Range("C1").Value
icompare = "01/04/2025"
icomdate = IsDate(iday)
ilater = IIf(iday > icompare, "Later", "Earlier")
MsgBox "Value of date cell: " & iday & " Value of Compare: " & icompare & " Rule " & ilater & " " & icomdate, vbOKOnly + vbInformation, "date"
End Sub
With a value of 24/04/2025 in cell C1.
It seems to come up with iday being earlier when it should be later. The format of C1 is Short Date
What happens if you use
icompare = #4/1/2025#
(VBA uses USA date format m/d/yyyy)
Also, I'd declare the variables explicitly:
Dim iday As Date, icompare As Date, icomdate As Boolean, ilater As String
HansVogelaar Thanks very much. I will have a try with these points later today.
@HansVogelaar Thanks very much for your advice. Just had to define the variables as dates as you suggested - did the job.
