Forum Discussion

Secretary960's avatar
Secretary960
Copper Contributor
Jul 11, 2024

Comparing dates in VBA

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 respon...
excel
Macros and VBA
HansVogelaar's avatar
HansVogelaar
MVP
Jul 11, 2024

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
  • Secretary960's avatar
    Secretary960
    Copper Contributor
    Jul 11, 2024

    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

     

    • HansVogelaar's avatar
      HansVogelaar
      MVP
      Jul 11, 2024

      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

      • Secretary960's avatar
        Secretary960
        Copper Contributor
        Jul 12, 2024

        HansVogelaar Thanks very much. I will have a try with these points later today. 

Resources