Unfortunately Excel doesn’t have an ISDATE function. Excel’s macro language, VBA does, but there is no spreadsheet function that let’s you know if a cell contains a date. Well there is a partial workaround and you can also use VBA.
We can use Excel’s CELL function to identify cells that are formatted as dates. This technique is based on a cell’s format and isn’t perfect. Also it doesn’t automatically update when a cell’s format changes because changing formats won’t trigger a recalculation.
Take the list of entries below. In column B we want to identify if column A contains a date.
The formula in cell B2 which has been copied down is
This extracts the first character from the CELL function and compares it to D.
Column C has the CELL function by itself. The formula in cell C2, also copied down, is
Note that cells formatted as dates return codes starting with D.
When you enter a date into a cell Excel will automatically apply a date format. Also entering a value into a cell with trigger a recalculation (after you press Enter).
The above formula works well in most cases, but it has some issues that are discussed below.
Row 5 is showing TRUE. Even though the cell is blank (like row 9) there is a date format on cell A5. The CELL function returns the format, not the contents of the cell. Cell A9 is bank but it has no format.
We can handle this issue with an amendment to the formula. Column D below has the following formula.
It’s a bit longer but it does the trick.
Row 13 is a problem. This is formatted with a fairly complex date format, but the format is not recognised by the CELL function as a date – not sure why.
For this technique to work you need to use standard, simple date formats.
There is a VBA solution. The file needs to be saved as a Macro enabled or a Binary file.
Press Alt + F11.
In the new window that opens click the Insert menu and choose Module.
In the window that opens on the right enter this code.
Function IS_DATE(rng) As Boolean IS_DATE = IsDate(rng) End Function
Then you can use the function IS_DATE function in a cell – see image below. The formula in cell E2 is
This is called a User Defined Function or UDF. This UDF can only be used in this file. To use it in another file you must create it the same way in the other file.