ISDATE Function – Google Sheets

Adam Steinfurth

The ISDATE function in Google Sheets is a logical function that returns TRUE if the given value is a valid date and FALSE otherwise. You can use it to validate data, perform calculations on dates, and create conditional formatting rules.

Syntax

The syntax for the ISDATE function is:

=ISDATE(value)

  • value : The cell reference or value that you want to check.
  • ISBLANK – Determines if a cell is empty
  • ISDATE – Determines if a value is a valid date
  • ISERROR – Determines if a value is an error
    • ISERR – Determines if a value is an error other than #N/A
    • ISNA – Determines if a value is an #N/A error
  • ISFORMULA – Determines if a value is a formula (which TYPE cannot do)
  • ISLOGICAL – Determines if a value is boolean
  • ISNONTEXT – Determines if a value is not text
  • ISNUMBER – Determines if a value is a number
  • ISREF – Determines if the value is a valid reference
  • ISTEXT – Determines if a value is text
  • ISURL – Determines if a value is a valid website address

Examples

Here are some examples of how to use the ISDATE function:

Example 1 – Check if a Specific Date Is Valid

Let’s start simply and see if “10-11-2023” is a date. Notice that you must surround any dates in quotes if you input them directly into the function. Read more about how to use dates in formulas to learn more.

The ISDATE function checking a value and returning TRUE
Checking a Date by Direct Input

Formula used: =ISDATE("10-11-2023")

The ISDATE function returns TRUE because "10-11-2023" is a valid date format.

Example 2 – Check if a Cell Contains a Valid Date

Next, let’s use a cell reference as input.

Checking a cell with the ISDATE function to see if it contains a valid date
Checking a Date with a Cell Reference

Formula used: =ISDATE(B1)

In this formula, Google Sheets looks at the value in cell B1, determines it is a date, and returns TRUE.

Example 3 – Validate a Data Range

You can use the ISDATE function to ensure that all values in a range are valid dates. Let’s do that for columns B and C below.

Using ISDATE to check the validity of dates in a range
Checking Ranges of Dates

Formula in cell B1: =ISDATE(B4:B7)

Formula in cell C1: =ISDATE(C4:C7)

If any values in the two ranges are not valid dates, the formula will return FALSE. All the values in B4:B7 are valid dates, so the formula in cell B1 returns TRUE. However, the value in C5 is not a date, so the formula in cell C1 returns FALSE.

Example 4 – Create Conditional Formatting Rules

You can use the ISDATE function to create conditional formatting rules. For example, you could highlight all of the cells in a range that contain invalid dates using the following conditional formatting rule:

Format cells if...
Custom formula is:
=NOT(ISDATE(A1))

This is what the formula looks like in Google Sheets when entered as a conditional formatting rule.

Using the ISDATE function in conditional formatting
Conditional Formatting

The completed conditional formatting rule changes the background fill of cell B2 to green because it is not a valid date.

Live Examples in Google Sheets

Get a copy of the spreadsheet with these examples to use yourself.

Conclusion

The ISDATE function is a powerful tool that you can use to validate data, perform calculations on dates, and create conditional formatting rules. By understanding how to use the ISDATE function, you can improve the accuracy and efficiency of your spreadsheets.