Table of Contents
To check if a cell in Excel contains a date value, one can use the ISDATE function, which will return TRUE if the cell contains a valid date value and FALSE otherwise. This function should be entered as a formula in the cell and the cell reference of the cell being checked should be used as the argument. For example, ISDATE(A1) will check if cell A1 contains a date value.
You can use the following formula to check if a specific cell in Excel contains a valid date:
=ISNUMBER(DATEVALUE(A2))
This particular example checks if cell A2 contains a valid date.
If it does, then the formula returns TRUE.
Otherwise, the formula returns FALSE.
Note that the function DATEVALUE converts a text date into a serial number.
If this function is unable to convert a text date into a serial number, then #VALUE! is returned.
We then wrap this function with the ISNUMBER() function to determine if the cell contains a number or not and return TRUE or FALSE as a result.
The following example shows how to use this formula in Excel.
Example: Check if Cell Contains Date in Excel
Suppose we have the following list of text values in Excel:
Note: It’s important that the values in column A are formatted as text before using the formula.
We’ll use the following formula to check if each cell in column A contains a valid date:
=ISNUMBER(DATEVALUE(A2))
We’ll type this formula into cell B2 and then copy and paste it down to every remaining cell in column B:
For example, we can see:
- “Hey” is not a valid date.
- “10/14/2023” is a valid date.
- “10/32/2023” is not a valid date since October 32nd doesn’t exist.
- “12/25/2023” is a valid date.
- “14/10/2023” is not a valid date since there are not 14 months in a year.
- “1/12/2023” is not a valid date.
If you would like to return values other than TRUE or FALSE, you can wrap the formula in an IF function:
=IF(ISNUMBER(DATEVALUE(A2)), "Valid", "Not Valid")
We’ll type this formula into cell B2 and then copy and paste it down to every remaining cell in column B:
Each value in column B now returns “Valid” or “Not Valid” to indicate if the value in the corresponding cell in column A is a valid date.