Excel ISDATE Function

A few solutions

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

=LEFT(CELL("format",A2))="D"

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

=CELL("format",A2)

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.

Problems

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.

=AND(A2<>"",LEFT(CELL("format",A2))="D")

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

=IS_DATE(A2)

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.

Download Example File

Please note: I reserve the right to delete comments that are offensive or off-topic.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

5 thoughts on “Excel ISDATE Function