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 response will trigger alternative code being executed. 

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

Resources