Comparing dates in VBA

Copper Contributor

I have a date entered on a form using this format: "22/02/2024 13:20" and I want to compare it to a specific date to see if it is earlier, later or the same. This needs to be doe in VBA as the response will trigger alternative code being executed. 

5 Replies


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"
        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.