Excel VBA IsDate function mistakes text for date

Question

I'm trying to perform date calculations in Excel VBA, but I'm getting a runtime error 13 (type mismatch) even though Excel's IsDate() returns true.

In Excel, I have the following data
A2 : enter "5/20" displays as "20-May"
A3 : enter "5/20 12:00 p" displays as "2019/05/20 12:00"
A4 : enter "5/20 12:00p" displays as "5/22 12:00p"

I have verified that cells A2 and A3 can be displayed as a number instead of a date. Cell A4 only displays as text/string, but the VBA IsDate() still passes on A4.

Private Sub Date_Errors()
Dim MyRng As Variant
Set MyRng = ActiveSheet.Range("A2", "A4")
For Each cell In MyRng
    If IsDate(cell) Then ' make sure the cell has a date
        If cell.Value < Date Then
            MsgBox ("Before")
        ElseIf (cell.Value - 1) < Date Then
            MsgBox ("Older")
        End If
    Else
        MsgBox ("Not a date")
    End If
Next
End Sub

MsgBox's get displayed properly for cells A2 and A3, but the mismatch error comes on the "ElseIf (cell.value - 1)" line. Why isn't IsDate(cell) catching this and falling through to the Else?

Why isn't IsDate(cell) catching this and falling through to the Else? Because that's not how IsDate works. The value you've got actually can be parsed as a date. Your problem is not related to that :) — David Zemens

Damian Damian · Accepted Answer · 2019-05-21T15:20:51

From microsoft help:

Dim MyVar, MyCheck
MyVar = "04/28/2014"    ' Assign valid date value.
MyCheck = IsDate(MyVar)    ' Returns True.

MyVar = "April 28, 2014"    ' Assign valid date value.
MyCheck = IsDate(MyVar)    ' Returns True.

MyVar = "13/32/2014"    ' Assign invalid date value.
MyCheck = IsDate(MyVar)    ' Returns False.

MyVar = "04.28.14"    ' Assign valid time value.
MyCheck = IsDate(MyVar)    ' Returns True.

MyVar = "04.28.2014"    ' Assign invalid time value.
MyCheck = IsDate(MyVar)    ' Returns False.

In other words, it will catch anything ressembling to a date, but actually you have a string so you can't substract 1 from a string. The solution?

Dim MyDate As Date
If IsDate(Cell) Then
MyDate = Cell

Then you use MyDate instead Cell

Excel VBA IsDate function mistakes text for date

1 Answers