vba - IsDate function returns unexpected results

Question

How come IsDate("13.50") returns True but IsDate("12.25.2010") returns False?

Answer 1

I got tripped up by this little "feature" recently and wanted to raise awareness of some of the issues surrounding the IsDate function in VB and VBA.

The Simple Case

As you'd expect, IsDate returns True when passed a Date data type and False for all other data types except Strings. For Strings, IsDate returns True or False based on the contents of the string:

IsDate(CDate("1/1/1980"))  --> True
IsDate(#12/31/2000#)       --> True
IsDate(12/24)              --> False  '12/24 evaluates to a Double: 0.5'
IsDate("Foo")              --> False
IsDate("12/24")            --> True

IsDateTime?

IsDate should be more precisely named IsDateTime because it returns True for strings formatted as times:

IsDate("10:55 AM")   --> True
IsDate("23:30")      --> True  'CDate("23:30")   --> 11:30:00 PM'
IsDate("1:30:59")    --> True  'CDate("1:30:59") --> 1:30:59 AM'
IsDate("13:55 AM")   --> True  'CDate("13:55 AM")--> 1:55:00 PM'
IsDate("13:55 PM")   --> True  'CDate("13:55 PM")--> 1:55:00 PM'

Note from the last two examples above that IsDate is not a perfect validator of times.

The Gotcha!

Not only does IsDate accept times, it accepts times in many formats. One of which uses a period (.) as a separator. This leads to some confusion, because the period can be used as a time separator but not a date separator:

IsDate("13.50")     --> True  'CDate("13.50")    --> 1:50:00 PM'
IsDate("12.25")     --> True  'CDate("12.25")    --> 12:25:00 PM'
IsDate("12.25.10")  --> True  'CDate("12.25.10") --> 12:25:10 PM'
IsDate("12.25.2010")--> False '2010 > 59 (number of seconds in a minute - 1)'
IsDate("24.12")     --> False '24 > 23 (number of hours in a day - 1)'
IsDate("0.12")      --> True  'CDate("0.12")     --> 12:12:00 AM

This can be a problem if you are parsing a string and operating on it based on its apparent type. For example:

Function Bar(Var As Variant)
    If IsDate(Var) Then
        Bar = "This is a date"
    ElseIf IsNumeric(Var) Then
        Bar = "This is numeric"
    Else
        Bar = "This is something else"
    End If
End Function

?Bar("12.75")   --> This is numeric
?Bar("12.50")   --> This is a date

The Workarounds

If you are testing a variant for its underlying data type, you should use TypeName(Var) = "Date" rather than IsDate(Var):

TypeName(#12/25/2010#)  --> Date
TypeName("12/25/2010")  --> String

Function Bar(Var As Variant)
    Select Case TypeName(Var)
    Case "Date"
        Bar = "This is a date type"
    Case "Long", "Double", "Single", "Integer", "Currency", "Decimal", "Byte"
        Bar = "This is a numeric type"
    Case "String"
        Bar = "This is a string type"
    Case "Boolean"
        Bar = "This is a boolean type"
    Case Else
        Bar = "This is some other type"
    End Select
End Function

?Bar("12.25")   --> This is a string type
?Bar(#12/25#)   --> This is a date type
?Bar(12.25)     --> This is a numeric type

If, however, you are dealing with strings that may be dates or numbers (eg, parsing a text file), you should check if it's a number before checking to see if it's a date:

Function Bar(Var As Variant)
    If IsNumeric(Var) Then
        Bar = "This is numeric"
    ElseIf IsDate(Var) Then
        Bar = "This is a date"
    Else
        Bar = "This is something else"
    End If
End Function

?Bar("12.75")   --> This is numeric
?Bar("12.50")   --> This is numeric
?Bar("12:50")   --> This is a date

Even if all you care about is whether it is a date, you should probably make sure it's not a number:

Function Bar(Var As Variant)
    If IsDate(Var) And Not IsNumeric(Var) Then
        Bar = "This is a date"
    Else
        Bar = "This is something else"
    End If
End Function

?Bar("12:50")   --> This is a date
?Bar("12.50")   --> This is something else

Peculiarities of CDate

As @Deanna pointed out in the comments below, the behavior of CDate() is unreliable as well. Its results vary based on whether it is passed a string or a number:

?CDate(0.5)     -->  12:00:00 PM
?CDate("0.5")   -->  12:05:00 AM

Trailing and leading zeroes are significant if a number is passed as a string:

?CDate(".5")    -->  12:00:00 PM 
?CDate("0.5")   -->  12:05:00 AM 
?CDate("0.50")  -->  12:50:00 AM 
?CDate("0.500") -->  12:00:00 PM 

The behavior also changes as the decimal part of a string approaches the 60-minute mark:

?CDate("0.59")  -->  12:59:00 AM 
?CDate("0.60")  -->   2:24:00 PM 

The bottom line is that if you need to convert strings to date/time you need to be aware of what format you expect them to be in and then re-format them appropriately before relying on CDate() to convert them.