Query and exclude empty cells

user_3896631 0
Viewed 43

I'm trying to query data, format it, and exclude empty cells.

enter image description here

=ARRAYFORMULA({"Date";IFERROR(QUERY({text(J2:J, "m/d/yyyy")}))})

Currently using this.

Tried using this:

=ARRAYFORMULA({"Date";IFERROR(QUERY({text(J2:J, "m/d/yyyy"), "where J <>''"}))})

But cells don't fill.

enter image description here

Not sure what I'm missing.

google-sheets
1 Answers

If you data in J Column are true date then you can use below QUERY() formula.

=QUERY(J2:J,"select J where J is not null label J 'Date' format J 'm/d/yyyy'")

Edit: If data is string formatted then could try-

=ArrayFormula(QUERY(DateValue(J2:J),"select Col1 where Col1 is not null label Col1 'Date' format Col1 'm/d/yyyy'"))

enter image description here

user_55147470
Related