Understanding The IsDate Function

Category: General VBA | [Item URL]

VBA's IsDate function supposedly tells you if a text string can be interpreted as a date. For example, all of the following expressions evaluate to True:

IsDate("5/25/2008")
IsDate("January 16")
IsDate("12-1")
IsDate("12/1/08")
IsDate("2/30")
IsDate("30/2")

Notice, in the last two examples, that IsDate isn't picky about the order of the day and month. Both of these strings could be interpreted as a date, so IsDate returns True (regardless of your system date format settings).

Here's some information from a Microsoft Support article.

The VBA date functions IsDate, Format, CDate, and CVDate utilize a function found in OLE Automation (OleAut32.dll). This function searches all possible date formats by tokenizing each of the separated values in the string representing the date and returns a Boolean value indicating whether the input can be represented as a Date.

This is important to remember when using the function to interpret a date that contains a 2 digit year. Different Locales use various date formats (that is, mm/dd/yy, yy/mm/dd, "DD MMM YY", "YY MMM DD", and so forth) and therefore the function tries the digits in all positions until the function has found a valid date or exhausted all possibilities.

Just because IsDate recognizes a string as a date doesn't mean that the string can be reliably converted to a date. In some cases, the result is ambiguous. For example, what about this expression?

IsDate("29-Feb-01")

February 29, 2001 is not a valid date. However, this expression returns True because February 1, 1929 (and January 2, 1929) are valid dates. And so are those same dates in 2029.

If IsDate is documented somewhere, I couldn't find it. Based on my testing, IsDate accepts any of the following as separator characters: a slash (/), a hyphen (-), a comma (,), a dot (.), and a space.

Therefore, the following expressions all return True:

IsDate("5.1")
IsDate("30 6")
IsDate("30,6")
IsDate("1/2")

But then there's this anomaly. The following expressions returns True:

IsDate("5.1.5")
IsDate("5.1.05")

But, inexplicitly, this expression returns False:

IsDate("5.1.2005")

Suppose that you created a UserForm with an InputBox where the user enters a date. It should be clear that using IsDate to validate the entry isn't very reliable.

Things get even more confusing when you realize that IsDate also covers time values (there is no corresponding IsTime function). So, the following expressions all return True:

IsDate("4:45")
IsDate("4.45")
IsDate("4 45")
IsDate("4/45")
IsDate("23:59")

These expressions return False:

IsDate("4:60")
IsDate("24.45")

It's important to point out that IsDate doesn't exhibit all of these quirks when you pass a Range argument. For example:

IsDate(Range("A1"))

As far as I can tell, IsDate is perfectly reliable at identifying cells that contains a date or a time. It does not, for example, identify a cell that contains 5.1 as a date.