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

@Secretary960 

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

 

@Secretary960 

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.