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:
The values in column B tell us whether each corresponding value in column A is a valid date or not.
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.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
Excel: How to Check if Range Contains Specific Value
Excel: How to Check if Cell Contains Partial Text
Excel: How to Check if One Column Value Exists in Another Column
