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

0
197
How to Convert Month in Number to Month Name in Text

Month wise summary is something you may badly require to create monthly reports in your office. In Google Sheets, it’s possible in so many ways. The most efficient way of crating month wise summary in Google Sheets is by using Query Function, even though Pivot Table is there to use. But many users are not aware how to use month in Query. So before going to our tutorial on how to convert Month in Number to Month Name in Text in Google Sheets, I suggest you to go through my earlier tutorial that follows.

Must Read: How to Use Month Function in Google Sheets Query

The Query function in Google Sheets can use to summarize data month wise. But there is a drawback. It summarizes the data based on month number. I mean 0 for January, 1 for February and so on.

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 reports. But there is an alternative formula that you can depend to create month name wise summary in Google Doc. It’s by using SUMIF function.

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

So as stated above you have several choices to prepare month wise summary in Google Doc Spreadsheets. But we can use Query formula itself to convert month in number to month name in text. Here we are going to learn this awesome Query trick. Also, using this trick, we are creating a month name wise summary in Google Sheets.

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

To make you understand this concept, I’m taking you to a quick tutorial below where I first make a month wise summary using Query function. But remember that the month here in month wise summary would be in number. Also you can go to my earlier tutorial regarding the same for detailed info. You can find the link above. After this tutorial, I’ll explain you how to convert this month in number to month name in text. So here we go.

Google Sheets Query: Month Wise Summary, But Month in Number.

Example:

See the example and the Google Sheets Query formula in F1.

Month Wise Summary, But Month in Number

In Column F, the number 1,2,3 and 5 represents months January, February, March and May. But it’s in number.

I am not detailing this tutorial here as I’ve already explained this month wise summary in the above mentioned tutorial (link given at the  beginning of this tutorial).

Now what I want to do is, I want the above month number in month name. We are going to learn this trick here. The formula what we are going to use here is entirely different and may unfamiliar to you.

Google Sheets Query: How to Convert Month in Number to Month Name in Text (Month Wise Summary)

Note:

Here I’m not only going to convert month in number to month in text, but also to summarize the data month wise.

I am using the above same sample data here for our example purpose. The data shows the name of few people who have taken appointments for a scheduled meeting in a future date. I want to know the number of appointments in each month. That means the month wise count of Column A.

So let’s learn this tutorial step by step. You can expect enough screenshots to learn this easily. The below is the formula that do the trick. Let me explain it.

=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'”)

Note: Copy and paste may not work. If you copy re type all double quotes.

Actually the above is three formulas that joined together.

Formula 1

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

This returns the month in number as below.

Query returns the month in number

Formula 2

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

This is the important part of the formula. This formula is an answer to how to convert month in number to month name in text. This would return the following result. But 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.

formula to convert month in number to month name in text

How to Summarize Month Name Wise in Query.

We have now formula one and two. I’ve joined These two arrays using curly brackets.

You May Like: How to Use Curly Brackets to Create Arrays in Google Sheets

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

Formula 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. Just scroll back and see the color pattern.

Important note: We can use this formula that without using the formula in red color (formula 1). But you may not get the month in order like January, February.. December as it’s text.

Query formula to summarize the data month wise

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

Now in the final formula, that you can see in the beginning of this tutorial, using another query, we can exclude this column 1. That’s all.

This way you can convert and summarize month number to month text in Google Sheets. Hope you find this tutorial useful. Please drop your views in comments.

LEAVE A REPLY

Please enter your comment!
Please enter your name here