How do I change the date format in a Google Sheets query pivot table with date filters?-CodePudding

I'm trying to change the date format in a google sheets query pivot table with date filters but I can't seem to find the right formula.

This is my data:

The table I am trying to create would be: grouping column B with the date as the first row using the same date format ex: "Jan 2021". Also using the date filters in B1 and B2.

I am able to create the pivot table using this formula:

=QUERY(A4:C11, "SELECT B, SUM(C) WHERE B IS NOT NULL AND A >= date """&text(B1, "yyyy-MM-dd")&""" AND A <= date """&text(B2, "yyyy-MM-dd")&""" GROUP BY B PIVOT A",1)

And this is what I get:

However, every time I try to add Format after Pivot A I get an error: "Format col not in select A"

How do I change the date format to ex:"Jan 2021" Thank You.

CodePudding user response：

you can do:

=INDEX(QUERY({A4:C11, TEXT(A4:A11*1, "mmm yyyy")}, 
 "select Col2,sum(Col3) 
  where Col2 is not null 
    and Col1 >= date '"&TEXT(B1, "yyyy-MM-dd")&"' 
    and Col1 <= date '"&TEXT(B2, "yyyy-MM-dd")&"' 
  group by Col2 
  pivot Col4", 1))

but as you can notice this won't be sorted as you should expect

so you can do:

=INDEX(REGEXREPLACE(""&QUERY({A4:C11, TEXT(A4:A11*1, "yyyymmdd×mmm yyyy")}, 
 "select Col2,sum(Col3) 
  where Col2 is not null 
    and Col1 >= date '"&TEXT(B1, "yyyy-MM-dd")&"' 
    and Col1 <= date '"&TEXT(B2, "yyyy-MM-dd")&"' 
  group by Col2 
  pivot Col4", 1), "^(.*×)", ))