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

Question

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.

Answer 1

Posting this for a clear answer that @MikeSteelson answered above!

I was originally bringing in the date, the month, and year in three separate columns. I did not need to do that as he mentions; I only need to have one column with the date. This allows me to format the data as a Date, which is important for the Pivot Table.

Once I created the Pivot table I set it up like this.

Then I right clicked on any cell in the first column, mine was labeled as "Received Date" and selected Create Pivot Group Date and then selected Month. IMPORTANT: In order to have this option show up in Google Sheets the dates must be formatted as a Date! If it is not then this option will not show for you. I then added a new column in the Pivot Table and clicked on any cell in the row for the new column. And selected Create Pivot Group Date and then selected Year.

It looks like this.

This also fixes the problem of having the Pivot Table sort chronologically and not alphabetically. I hope I was able to explain how I fixed this thanks to the help of @MikeSteelson and @player0

Answer 2

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

---

update:

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

Answer 3

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.