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

Question

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.

player0 · Accepted Answer · 2021-11-09T23:22:29.333

1

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), "^(.*×)", ))

edited Nov 09 '21 at 23:22

answered Nov 08 '21 at 23:53

player0

124,011
12
67
124

Hi sorry for asking again but I'm doing another table with the same formula but different data. What did you put in Col4? Im getting the same problem again with the date format. – Mari Rose Nov 09 '21 at 22:15
Also the sum(Col3) will not let me add a currency format. – Mari Rose Nov 09 '21 at 22:39
@MariRose see: https://i.stack.imgur.com/RDTVl.png – player0 Nov 09 '21 at 23:21
@MariRose as for the currency & sum(Col3)... can you share a copy/sample of your sheet? – player0 Nov 09 '21 at 23:23
I'll use the first data image as an example. I'm trying to import data from two different sheets onto a third sheet. The date filters "Jan 2021" and "Mar 2021" are on Sheet3. The data is on Sheet20. When I use the formula: =INDEX(REGEXREPLACE(""&QUERY({'Sheet20'!J2:M, TEXT('Sheet20'!J2:J*1, "yyyymmdd×mmm yyyy")}, "select Col2,sum(Col3) where Col2 is not null and Col1 >= date '"&TEXT('Sheet3'!C5, "yyyy-MM-dd")&"' and Col1 <= date '"&TEXT('Sheet3'!C9, "yyyy-MM-dd")&"' group by Col2 pivot Col1", 1), "^(.*×)", )) – Mari Rose Nov 11 '21 at 22:25
I get the same table as the second image but without the "$" currency on the Price column. I would like to keep the same date format "Jan 2021" and keep the "$" currency from the Price column. Thank You! – Mari Rose Nov 11 '21 at 22:25
@MariRose see: https://docs.google.com/spreadsheets/d/1eycI6WMyoJHD382Nd3E8mDI7tuRvD9Fm0qKaqdSxN5Y/edit#gid=0 – player0 Nov 11 '21 at 22:51
1

Thank you so much. That is exactly what I needed. Thank you! – Mari Rose Nov 12 '21 at 03:05
@player0 what about descending order? If I want to sort like `Apr 21-->Mar 21-->Feb-21-->Jan 21`. Any suggestion. – Harun24hr Jun 24 '22 at 06:03
@Harun24hr it is possible but you will need to add ascending sequence to descending dates and join it with unique symbol so it will look like: 00001♥Apr 21 and then after pivot you just use REGEXREPLACE to remove it: "\d+♥" – player0 Jun 24 '22 at 12:36
@player0 Can you please make a full formula for me on your example sheet shared on comment here. I tried but couldn't figure out. – Harun24hr Jun 26 '22 at 03:33
@Harun24hr see F13 – player0 Jun 26 '22 at 07:56

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

1 Answers1

Linked