The MONTH scalar function in Query returns month numbers. How do we convert the month number to the month name in Google Sheets Query?

Before starting, I suggest you read one of my earlier tutorials, i.e., How to Use Month Function in Google Sheets Query. That can give you some idea about the month number in Query. 

Then we can discuss how to convert or format the month number to the month name in Query.

Introduction

You may badly require month-wise summary reports (part of progress reports) to present in your client meetings. We can create such reports in 2-3 ways in Google Sheets.

The most efficient way is by using Query Function, and the easiest way is the Pivot Table. But many users are unfamiliar with grouping data by month text (name) in Query.

You May Like:- Month, Quarter, Year Wise Grouping in Pivot Table in Google Sheets

The Query function in Google Sheets can summarize data month-wise. But there is a drawback if you use the MONTH scalar function.

It summarizes the data based on 0-11 month numbers. I mean 0 for January, 1 for February, and so on.

Other than this, we can use the native MONTH function outside Query. Then it will return month numbers 1 to 12.

Similar: Month Wise Summary in Google Sheets Using Query Formula

The above Google Sheets tutorial can help you to prepare this type of (month-number-wise) month-wise summary report.

But there is an alternative formula that you can depend on to create the month name-wise summary in Google Sheets. That is by using the SUMIF function.

Tips: Sum by Month in Google Sheets Using Combined SUMIF Formula

So, as stated above, you have several choices to prepare a month-wise summary in Google Sheets.

In this tutorial, our focus is converting the month numbers returned by the MONTH (native) and MONTH scalar functions to the month names in Query.

Month Wise Summary, But Month in Number: The Problem

Let’s find the number of entries each month by grouping the dates in column A by month. The following formula in F1 will do that.

=query(A2:A,"Select month(A)+1,count(A) where month(A)+1>0 group by month(A)+1 label month(A)+1 'Month No.'")

In column F, the numbers 1, 2, 3, and 5 represent months January, February, March, and May, respectively.

In the above Google Sheets Query output, I want the month numbers in the corresponding month names.

We can follow two approaches to do that.

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

Note:- This is our approach # 1, though, I didn’t mention it in the title above. In this, we will use the native MONTH scalar function.

Here, we will convert month in number to month in the text format in Query. Also, we will summarize the data month-wise on the go.

I am using the above same sample data here for our example purpose.

The data shows the name of a few people who have taken appointments for a scheduled meeting.

I want to know the number of appointments each month. That means the month-wise count of column A.

Below is the formula that does the trick.

=query(query({query(A2:A,"Select Month(A)+1 label month(A)+1''"),ArrayFormula(if(len(A2:A),upper(text(A2:A,"MMM")),""))},"Select Col1, Col2, Count (Col2) where Col2<>'' group by Col1,Col2"),"select Col2,Col3 where Col2<>'' label Col2'Month'")

It’s not as complicated as you might think. Let’s go into detail.

Anatomy of the Formula

Formula Part 1:

=query(A2:A,"Select Month(A)+1 label month(A)+1''")

Part 1 returns the months in numbers as below.

Formula Part 2:

=ArrayFormula(if(len(A2:A),upper(text(A2:A,"MMM")),""))

This is an important part.

This part 2 formula is the answer to how to convert month in number to month name in text in Query.

I’m taking you to one more additional step to help you to make it possible to create a month name-wise summary using Query.

How to Summarize Month Name Wise in Query

We have two formula parts as above. I’ve joined These two arrays using curly brackets in the original formula.

So the result would be two columns side by. See that formula and screenshot below.

Formula Part 3:

=query({query(A2:A,"Select Month(A)+1 label month(A)+1''"),ArrayFormula(if(len(A2:A),upper(text(A2:A,"MMM")),""))},"Select Col1, Col2, Count (Col2) where Col2<>'' group by Col1,Col2")

This formula is to summarize the data month-wise.

Important note:- We can use it without formula part 1. But you may not get the month text in sequence like January, February, March, etc.

As you can see, the first column in the above result is not required in our final output.

With the help of another Query, we can exclude column 1, and that’s all.

This way, you can convert and summarize month number to month text in Google Sheets Query.

Convert Month in Number to Month Name in Text in Query: The EOMONTH Approach

In this, we will use the EOMONTH date function.

Here is another popular way of converting months in numbers to month names in text in Google Sheets Query.

In this approach, we will convert the dates in column A to the corresponding month start dates and group them. But all within one single Query formula.

=query(ArrayFormula(iferror(eomonth(A2:A,-1)+1)),"Select Col1,count(Col1) where Col1 is not null group by Col1 label Col1'Month' format Col1'MMM'")

The FORMAT clause in Query formats the dates to month texts.

But please bear in mind one thing. The underlying values in the formatted month text cells will be month start dates.

Note:- You can use the month’s start [ eomonth(A2:A,-1)+1 ] or end [ eomonth(A2:A,0) ] dates.

Here is a related tutorial: How to Group Data by Month and Year in Google Sheets.