Query and exclude empty cells-CodePudding

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

=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.

Not sure what I'm missing.

CodePudding user response：

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'"))