Using the TEXT function makes it easy to convert a date to month and year format in Google Sheets. However, retaining the converted month and year in date format requires a different approach.

The TEXT function converts the formatted value, whether it’s a date or number, into text. Thus, when you use this function to convert a date to month and year, the output will be in text format.

Such converted dates are sorted alphabetically, which can lead to unexpected outcomes when used to create summary reports using QUERY or Pivot Tables. Additionally, statistical functions like FORECAST may encounter errors in calculation.

In this post, I’ll outline two methods to convert a date to month and year in Google Sheets:

1. Using the TEXT Function.
2. Utilizing a combination of EOMONTH and Custom number formats which retains the underlying value as dates.

Before diving into the formulas, let’s examine the outputs of both methods below.

Column B contains the results of the TEXT function, whereas column C contains the results of the EOMONTH function and number formatting approach.

The values in column C are aligned to the right. That means the values are dates.

Convert Date to Month and Year in Text Format

There are several formats available for displaying month and year in text format, depending on your needs. We will choose some of the most common options for the converted text.

Let’s consider the date in cell A2 as 26/11/2019.

Numeric Style:

Two digits for the month (with leading zeros for single-digit months) and four digits for the year.

=TEXT(A2, "MM/YYYY") // returns 11/2019

Four digits for the year followed by the month with leading zeros, separated by a hyphen.

=TEXT(A2, "YYYY-MM") // returns 2019-11

Textual Style:

Full month name followed by a space and then the four-digit year.

=TEXT(A2, "MMMM YYYY") // returns November 2019

Abbreviated month name followed by a space and then the four-digit year.

=TEXT(A2, "MMM YYYY") // returns Nov 2019

Want to convert dates in a column to month and year using a single formula?

Enter the following array formula in cell B2:

=ArrayFormula(IF(A2:A="",,TEXT(A2:A, "MMM-YYYY")))

Replace the highlighted format in the formula with the format you prefer.

Convert a Date to Month and Year While Retaining the Underlying Value as a Date

I strongly advocate this EOMONTH and number formatting method for converting dates to months and years in Google Sheets. Since the underlying value remains a date, when grouping or sorting, they will be arranged chronologically.

In this approach, we will convert the date to the beginning of the month using the EOMONTH function.

For example, the following formula in cell C2 will convert the date in cell A2, which is 26/11/2019, to 01/11/2019.

=EOMONTH(A2, -1) + 1

Navigate to cell C2 and click on Format > Number > Custom number format

In the field, enter one of the following text formatting: MM/YYYY, YYYY-MM, MMMM YYYY, MMM YYYY.

Click Apply.

If the formatting is “MMMM YYYY,” the cell will show “November 2019,” but the underlying value will be 01/11/2019.

If you want to apply this to the whole range, use the following formula in cell C2:

=ArrayFormula(IF(A2:A="",,EOMONTH(A2:A, -1) + 1))

Apply the formatting to the entire result from the Format menu.

Why Should We Use EOMONTH Before Number Formatting?

I know you can directly convert any date to month and year in Google Sheets without using any formulas. You just need to apply the number formatting.

I mean, as per my example above, select the dates in the range A2:A and then apply Format > Number > Custom number format and apply your desired formatting. The issue with this approach is that we can’t group by month and year.

With the EOMONTH formula, we convert all dates to the beginning of the month dates. Then we apply the formatting. This ensures that the dates will act as one group for each month and year, creating distinct groups for different months and years.

Resources

1. Google Sheets Query: How to Convert Month in Number to Month Name in Text.
2. Formula to Convert Month Name in Text to Month Number in Google Sheets.
3. Convert Numbers to Month Name in Google Sheets.
4. Formula to Sort By Month Name in Google Sheets.
5. How to Find Current Month’s Week Number In Google Sheets.
6. How to Group Data by Month and Year in Google Sheets.