The ISDATE function in Google Sheets is a logical function that returns TRUE if the value is a valid date and FALSE otherwise. You can use it to validate data, perform calculations on dates, and create conditional formatting rules.
The syntax for the ISDATE function is:
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 whether 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
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 returns TRUE because
"10-11-2023" is a valid date format.
Example 2 – Check if a Cell Contains a Valid Date
The function also accepts a cell reference as input.
In this formula, Google Sheets looks at the value in cell
B1, determines that 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
Formula in cell B1:
Formula in cell C1:
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.
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.
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.