Google Sheets Query: How to Convert Month in Number to Month Name in Text-CodePudding

I have a very large sheet full of data that I am trying to make a dynamic dashboard for. Currently I have a QUERY formula pulling in the needed data from the master sheet.

=QUERY('COPY OF MASTER BOOK'!A1:Z,"
SELECT A, B, C, MONTH(C) 1, YEAR(C) ,E, MONTH(E) 1, YEAR(E), I, MONTH(I) 1, YEAR(I), K, N, MONTH(N) 1, YEAR(N), O, P, Q, R, S, T, U, V, W, X, Y
LABEL MONTH(C) 1'Shop Complete Month', YEAR(C)'Shop Complete Year',MONTH(E) 1'Shipped Month', YEAR(E)'Shipped Year',MONTH(I) 1'Received Month', YEAR(I)'Received Year',MONTH(N) 1'Approved Month', YEAR(N)'Approved Year'")

This is pulling in everything that I need. If you see I am putting MONTH(C) 1 to pull the month out from the date. It returns the month in digit form. I would like it to be in text form so when I make a pivot table it can show the name instead of the number. I will attach some pictures of the data my QUERY formula is pulling in and what my pivot table looks like.

Output of QUERY formula. A small sample of the output.

Pivot table with numbers. Need month names!

How can I make the QUERY formula show month names instead of numbers? I've been working on this all day and can't find a solution on any forum online that works for my situation. Other solutions add the month at the beginning of the query formula or add a formula in the cells outside the query. None of those work because I need to insert the name in the column the numbers are in because my QUERY formula is so large.

CodePudding user response：

try:

=ARRAYFORMULA({'COPY OF MASTER BOOK'!A2:B, 
 TEXT('COPY OF MASTER BOOK'!C2:C, {"m/d/yy", "mmmm", "yyyy"}), 
 TEXT('COPY OF MASTER BOOK'!E2:E, {"m/d/yy", "mmmm", "yyyy"}), 
 TEXT('COPY OF MASTER BOOK'!I2:I, {"m/d/yy", "mmmm", "yyyy"}), 'COPY OF MASTER BOOK'!K2:K, 
 TEXT('COPY OF MASTER BOOK'!N2:N, {"m/d/yy", "mmmm", "yyyy"}), 'COPY OF MASTER BOOK'!O2:Y})

or:

=ARRAYFORMULA(QUERY({'COPY OF MASTER BOOK'!A:B, 
 TEXT('COPY OF MASTER BOOK'!C:C, {"m/d/yy", "mmm", "yyyy"}), 
 TEXT('COPY OF MASTER BOOK'!E:E, {"m/d/yy", "mmm", "yyyy"}), 
 TEXT('COPY OF MASTER BOOK'!I:I, {"m/d/yy", "mmm", "yyyy"}), 'COPY OF MASTER BOOK'!K:K, 
 TEXT('COPY OF MASTER BOOK'!N:N, {"m/d/yy", "mmm", "yyyy"}), 'COPY OF MASTER BOOK'!O:Y}, 
 "label Col4'Shop Complete Month',
        Col5'Shop Complete Year',
        Col7'Shipped Month',
        Col8'Shipped Year',
        Col10'Received Month',
        Col11'Received Year',
        Col14'Approved Month',
        Col15'Approved Year'"))

CodePudding user response：

You do not need to create new columns with month by number/by text and year.

Create your pivot table, then group the dates by year and monthes. That's all you have to do. You will have the right sorting behavior and monthes in text or as you wish by formatting.