excel says date in June is greater than a date in July - why?

Question

I am new to VBA and I've written a code which gets a start and stop date from report X and retrieves data from report Y where the dates lie between the start and the stop date. (e.g. 18 Jun 2018 > data form report Y < 05 Jul 2018).

Here comes the issue: my VBA code does not run correctly since it tells me in an if-else-statement, that 18 Jun 2018 is greater than 05 Jul 2018, which cannot be true. I have tried formatting the dates but it still would't work.

Sub calculateCompliance()

n = 0
    ' calculate for screening
    For i = 1 To 500
        If Worksheets("Sheet1").Cells(i, 6) > Worksheets("Sheet2").Range("E5") _
        And Worksheets("Sheet1").Cells(i, 6) < Worksheets("Sheet2").Range("F5") _
        Then
                n = n + 1
        End If
    Next i

Worksheets("Sheet2").Range("E6").Value = n

End Sub

EDIT: By adding the variable declarations i.e. "dim startdate as Date", the errors have been resolved. This was really because of the different cell format. Thanks a lot!

Cast them to Date types. I'll bet anything you're doing a String compare. — Comintern
Can you post your actual code so we can see how you are reading it in? — Hasib_Ibradzic
I have formatted already both to Date format, they are not strings. — hoffmannv
Add the code to the original question - formatted properly... — Solar Mike
I've tested your code and it works exactly how you describe it should. I'm not getting 18 Jun 2018 as being greater than 05 Jul 2018. Listing each date from 01 Jun 2018 to 01 Aug 2018 in Sheet1 Column F, I get a result of 16 days between the two which is accurate. What is happening that would lead you to believe the comparison is not working? — pondersome

HackSlash HackSlash · Accepted Answer · 2018-07-30T21:07:25

Don't rely on cell types in code. Let's pull those values in and force VBA to coerce them in to Date values. Then do our comparison.

Does this work for you?

Sub calculateCompliance()
    Dim thisDate As Date
    Dim startDate As Date
    Dim endDate As Date

    startDate = Worksheets("Sheet2").Range("E5")
    endDate = Worksheets("Sheet2").Range("F5")

    Dim n As Integer
    n = 0
    ' calculate for screening

    Dim i As Integer
    For i = 1 To 500
        thisDate = Worksheets("Sheet1").Cells(i, 6)
        If thisDate > startDate And thisDate < endDate Then
            n = n + 1
        End If
    Next i

    Worksheets("Sheet2").Range("E6").Value = n
End Sub

excel says date in June is greater than a date in July - why?

2 Answers