CODEWITHSUNDEEP
google-sheets
cpaone
cpaone

Reputation: 135

Query and exclude empty cells

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.

Upvotes: 2

Views: 3812

Answers (1)

Harun24hr
Harun24hr

Reputation: 36860

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

Upvotes: 1

Related Questions