CODEWITHSUNDEEP
google-sheets
719016
719016

Reputation: 10431

Google Sheets FILTER formula for range where column is a date?

I have a Google Sheets worksheet Sheet1 where column E can be either a DATE, e.g. 2018-01-01 or NA or blank.

I want to create another worksheet Sheet2 with only the entries that look like a date, to then be able to create a Data Validation on another worksheet Sheet3 where the date column is only allowed to be entered if it's not already present in the combination of columns B and E in Sheet2.

I assumed I needed to use the =FILTER() function but when I try:

=FILTER(E:E,ISDATE(E:E))

I get:

FILTER has mismatched range sizes. Expected row count: 18769, column count: 1. Actual row count: 1, column count: 1.

Any ideas?

Upvotes: 1

Views: 3620

Answers (4)

stwheel1
stwheel1

Reputation: 1

Try:

=filter(A2:A,isnumber(year(A2:A)))

Upvotes: 0

J_A_PROP
J_A_PROP

Reputation: 11

I had a similar issue! I found another stack overflow answer and thought I should share it here.

This one worked for me:

=FILTER(E:E,ARRAYFORMULA(IF(ISDATE_STRICT({E:E}),TRUE,FALSE)))

Upvotes: 1

player0
player0

Reputation: 1

if this is your Sheet1:

...then all you need to do is:

=ARRAYFORMULA(TO_DATE(QUERY(VALUE(Sheet1!E:E), 
 "select Col1 where Col1 > 40000", 0)))

Upvotes: 1

bartinmader
bartinmader

Reputation: 285

ISDATE won't work because it will return a boolean instead of an array (if all values are dates, it will return true, if even a single one isn't, it will return false).

However, for FILTER you'll need a second array.

I think you could try something like:

=ArrayFormula(iferror(IF(DATEVALUE(E:E),TRUE),FALSE))

And then take it from there with FILTER.

Upvotes: 2

Related Questions