IsDate ArrayFormula

Viewed 2448

Have just noticed isDate does not work in arrayformula.

Case

Want to filter all values if dates:

enter image description here

Used the formula:

=FILTER(data,ISDATE(data))

Expected result:

8/28/2018

Got:

#N/A

Question

  1. Why? Other checks work in filter (isNumber, isText, isErr).
  2. Workarounds?
4 Answers

Please try:

=ARRAYFORMULA(ISDATE_STRICT(A1:A11))

enter image description here

The function is currently not documented.

: ISDATE_STRICT wont let date&time format, dates only

  1. Do not know the reason, still curious.

  2. Workaround: =FILTER(data,IFERROR(DATEVALUE(data))) was found here

Note: Workaround will NOT work for dates formatted as:

dd.mm.yyyy

You may use a duck-typed workaround:

=FILTER(data,REGEXMATCH(MID(data,7,4),"20\d{2}"))

Will check if formatted date has a 20XX year string inside.

A workaround that could work depending on what you're trying to do as far as Arrayformula is concerned

=ARRAYFORUMLA(ISNUMBER(VALUE(data)))
  • VALUE can turn the time into a number
  • ISNUMBER checks if it's a number
  • ISNUMBER works fine within ARRAYFORMULA

Now you can convert any formula to :

=BYROW(A2:A12,LAMBDA(r,ISDATE(r)))

Related