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

Related Posts

Posted on
Categories: Excel Blog, Functions, Macros

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.

12 thoughts on “Excel ISDATE Function”

  4. I am using this to test cell bd3. If there is a date, it will fetch that. If it is not a date, it will use the result of the today() function. Since the only two options are a date or text, this works for me.

    =IF(ISERROR(INT(BD3)),TODAY(),BD3)

    Basically, it performs a math function on the cell to be tested. If the function works; it’s a date. If not, it is text.
    One may be able to improve the test by passing the results of a different function to the iserr function.

    Reply

  5. This is no good. You can set the “format” of a cell to a date but not have a valid date in it. This function will report it is a “date” when the contents is not a date.

    The VBA is the best way to go, but you can also get by most of the time by using the text function in conjunction with INT, since a valid date is represented by a numeric value.

    For example, the value of 1/1/2022 is 44562. So if you are checking cell A2:

    =(ISERROR(INT(TEXT(A2,”0″)))=FALSE)

    This say, convert the value in A2 to text, then convert it to an integer, and if there is no error, then it’s a valid date. Obviously if a number actually is in A2, this would also report TRUE, but you could add an IIF to see if the value is between certain values.

    Reply