Why does my IsDate() If statement ignore "31/12/1019"-CodePudding

I am attempting to remove all dates before a certain time period, as well as anything that is not a date from my data set. I have roughly 4000 entries in column A dating back the last 10 years, with some typo's mixed in. There are no blanks spots between the data.

I have cobbled together the below code, which almost works. However there is one entry 31/12/1019 which is not being picked up as an old date, or a typo.

Sub deleterows()
    lastRow = Sheets("ConData").Cells(Rows.Count, 1).End(xlUp).Row
    bankingDate = DateSerial(Year(Date), Month(Date), 0)

    For i = lastRow To 1 Step -1
        If IsDate(Cells(i, 1)) = False Or _
           Cells(i, 1).Value <= bankingDate Then Rows(i).EntireRow.Delete
    Next
End Sub

Any help would be appreciated.

CodePudding user response：

Let's break it down, this is a wonderful inconsistency between Excel and VBA:

According to THIS article:

In Windows, the range of valid dates is January 1, 100 A.D., through December 31, 9999 A.D.; the ranges vary among operating systems.

so IsDate will return TRUE for 31/12/1019

But

Since Excel actually stores "dates" as a double with 1900-01-01 being 1.00 the date would be stored as a string in the worksheet and the Cells(i, 1).Value <= bankingDate would return False because a string is larger than a number.

But as @BigBen stated:

Cast to a date first before comparing to bankingDate.

If Not IsDate(Cells(i, 1).Value) Then
   Rows(i).EntireRow.Delete
ElseIf CDate(Cells(i, 1).Value) <= bankingDate
   Rows(i).EntireRow.Delete
End If

CodePudding user response：

IsDate will give true but you can check then for isNumeric(Cells(i, 1).value2 If not IsDate(Cells(i, 1)) or not isNumeric(Cells(i, 1).value2) or ... Value2 gives a double for Date Cells which are later then 0.1.1900 And I believe your banking date will always be later