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

Are you struggling with converting month numbers to month names in Google Sheets Query? Look no further! In this tutorial, we will explore two approaches to help you convert month numbers to month names effortlessly.

Introduction

When creating month-wise summary reports, it can be challenging to present the data in a meaningful way. While the Query function in Google Sheets can summarize data month-wise, it uses month numbers instead of month names. Don’t worry; we’ve got you covered!

Month Wise Summary, But Month in Number: The Problem

Let’s dive right into the problem. Say you want to find the number of entries each month by grouping the dates in column A by month. Using the following formula in cell F1 will give you the desired result:

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

However, the output displays month numbers instead of month names. So, how can we convert month numbers to month names in Google Sheets Query? Let’s find out!

Approach #1: Using the Native MONTH Scalar Function

This approach involves converting month numbers to month names within a Query. Follow these simple steps:

1. Use the following formula to get the month numbers and the corresponding month names:

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

   Don’t be intimidated; let’s break it down:

   • query(A2:A,"Select Month(A)+1 label month(A)+1''") returns the month numbers.
   • ArrayFormula(if(len(A2:A),upper(text(A2:A,"MMM")),"")) converts the month numbers to month names.
   • {query(A2:A,"Select Month(A)+1 label month(A)+1''"),ArrayFormula(if(len(A2:A),upper(text(A2:A,"MMM")),""))} combines both arrays.
   • The outer query function summarizes the data month-wise while excluding any empty values.

And that’s it! You now have your month-wise summary with month names instead of numbers.

Approach #2: Using the EOMONTH Function

If you prefer a different approach, we’ve got you covered. This time, we’ll utilize the EOMONTH date function. Follow these steps:

1. Use the following formula to convert the dates in column A to month start dates and group them:

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

   Let’s break it down:

   • ArrayFormula(iferror(eomonth(A2:A,-1)+1)) converts the dates to month start dates.
   • The outer query function summarizes the data month-wise while formatting the dates as month names using the FORMAT clause.

Now you have another way to convert month numbers to month names in Google Sheets Query.

That’s it! You are now equipped with two powerful methods to convert month numbers to month names in Google Sheets Query. Say goodbye to confusing month numbers and present your data in a more understandable format. Happy analyzing!

Remember, if you need more Google Sheets tips and tricks, visit Crawlan.com for all your Google Sheets needs.