Understanding The IsDate Function
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?
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:
But, inexplicitly, this expression returns False:
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:
It's important to point out that IsDate doesn't exhibit all of these quirks when you pass a Range argument. For example:
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.
Excel has a long history, and it continues to evolve and change. Consequently, the tips provided here do not necessarily apply to all versions of Excel.
In particular, the user interface for Excel 2007 (and later), is vastly different from its predecessors. Therefore, the menu commands listed in older tips, will not correspond to the Excel 2007 (and later) user interface.
Browse Tips by Category
Search for Tips
Needs tips? Here are two books, with nothing but tips:
Contains more than 200 useful tips and tricks for Excel 2007 | Other Excel 2007 books | Amazon link: John Walkenbach's Favorite Excel 2007 Tips & Tricks