Isdate Function and Better Alternative in Google Sheets

Published on

How to test whether a value in a cell is a date? Use the built-in Isdate function in Google Sheets.

Even though you can use the Isdate in a range of cells, it won’t return an array result. So we must also know about the alternative to Isdate function in Google Sheets.

The Isdate is just an Info type function. So it returns TRUE or FALSE.

Must Check: Learn all the date functions in Google Spreadsheets.

How to Use Isdate Function in Google Sheets

The below four formulas explain the use of the Isdate function in Google Sheets.

Example # 1:

=ISDATE("31/12/2018")

Result: TRUE

If you Sheets’ default date format is MM/DD/YYY, then you should try this formula as below.

=ISDATE("12/31/2018")

Mine is DD/MM/YY.

Example 2:

=ISDATE("December 2018")

Also you can use a cell reference in the Isdate.

=ISDATE(A1)

If the cell content is a date, then this formula would return TRUE else FALSE.

How to use Isdate Function in a Range of Cells in Google Sheets

Does the Isdate function return an Array Result?

Nope!

You can not use Google Sheets Isdate function to return an array result in each row in a column. But it checks all the values in a range (array) as a whole.

isdate in an array

The above Isdate formula in Google Sheets returns FALSE since the value in cell A3 is blank.

How to Check Values in a Column for Dates and Return an Array Result

As said, the Isdate() won’t return an array result. Here is one better alternative to the Isdate in Google Sheets.

Isdate Function Alternative to Return Array Result

The better alternative to the Isdate in Google Doc Sheets is the DATEVALUE function. But it’s a combination formula involving IF and IFERROR.

Example:

=IFERROR(IF(DATEVALUE(A1),TRUE),FALSE)

Or

=IFERROR(IF(DATEVALUE("31/12/2018"),TRUE),FALSE)

It does work in a range and it return an array result. See one example below.

Suppose I want to test the range A1: A8 for dates, and if the values are dates return TRUE in each row else FALSE.

Formula:

=ArrayFormula(iferror(IF(DATEVALUE(A1:A8),TRUE),FALSE))

Image:

is date expanding result

In this formula, you can replace the TRUE/FALSE Boolean values with any calculation that the formula to return.

Treat this tutorial as an alternative to your following queries too.

How to check the presence of dates in a column?

Isdate Array Formula equivalent?

Conclusion

Hope you have liked the above tutorial on the use of Isdate and the alternative combination formula in Google Sheets.

2019 is fast approaching. If you are looking for a 2019 calendar template in Google Sheets, here it’s.

Link: Free Google Sheets Calendar Template Download and How To.

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Hierarchical Numbering Sequences in Excel

Creating hierarchical numbering sequences in an Excel spreadsheet can significantly improve the way you...

How to Easily Repeat a Sequence of Numbers in Excel

Excel offers multiple ways to accomplish tasks, and the simplicity of each approach depends...

Create a Sequence of Dates at Every Nth Row in Excel (Dynamic Array)

Would you like to create a sequence of dates in every nth row in...

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

1 COMMENT

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.