Excel: How to Check if Cell Contains Date


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:

Excel check if cell is date

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

Leave a Reply

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