Convert Date to Month and Year in Google Sheets (A Comprehensive Guide)

In our guide today, we will see how to convert a date to month and year in Google Sheets. We have multiple approaches to this problem, each made easy, in terms of both understanding and accessibility, by Google Sheets.

How to Convert Date to Month and Year in Google Sheets

The crux of converting a date to any form rests on the fundamental formatting of values. Thankfully, Google Sheets has multiple ways that users can approach formatting data in their spreadsheets. One of which can be easily navigated from the Toolbar, while the other comes in the form of functions.

1. Convert or Format Date to Month and Year from the Google Sheets Toolbar

Using the Advanced Date and Time Feature (Custom Formatting)

Google Sheets provides its users with a hassle-free approach to format dates that can be performed right from the Toolbar.

You can navigate to it from the Format tab. Here you can find the “Custom date and time” option under the Number cluster. Format > Number > Custom date and time

navigating to custom date and time - convert date to month and year in google sheets

This should open the “Custom date and time formats” window.

the custom date and time formats window

As you can see, the window already has a multitude of formats to choose from. Or you can use the top panel to create your own date formats.

This is what we will be taking advantage of in this article.

Convert Date to Month and Year from Toolbar

Our target today is to convert a date to month and year in a spreadsheet. So, back in the “Custom date and time formats” window, we can make some changes. Let’s look at the process step-by-step. To cover the fundamentals, we have started from a clean slate by deleting the existing format tabs:

Step 1: Select the Month and Year tabs from the drop-down menu.

select the month and year tabs to format and convert in google sheets

Step 2: It is always a good idea to put a delimiter between the different sections. A delimiter can be anything from the default slash (/), dash (-), comma (,), or even a whitespace combination. Simply type in the desired delimiter between the Month and Year tabs.

adding a delimiter to our date format

Step 3: Select the drop-down icon of the Month and Year tabs.

the month and year tabs have drop-down options

Here, choose the type of formatting or output you want.

setting month as full name

We are going for the full month name and full year value.

Step 4: Click Apply to convert the date to only month and year in Google Sheets.

finalizing our date format before clicking apply

Our result:

convert date to month and year using custom formatting in google sheets

Using this approach, you can transform a date into any format that you desire while keeping the base values intact.

Learn More: How to Format Date in Google Sheets (3 Easy Ways)

2. Convert or Format Date with a Formula to Month and Year

Google Sheets also provides its users with a plethora of date functions that can be utilized in multiple yet niche scenarios. Let’s have a look at some of these scenarios and how we can use some of the functions available.

I. How to Extract Month and Year from a Date in Separate Columns

For this section, we will look to extract the month and year values in Google Sheets separately and place them in columns. For this, we have quite the intuitive functions: MONTH and YEAR. Their purpose, as you might have already guessed, is to extract their corresponding values from a date.

Using the MONTH function:

=MONTH(B3)

converting date to month only using the month function

Using the YEAR function:

=YEAR(B3)

converting date to year only using the year function

If you are looking to extract months by name, please see the next section.

II. Basic Date Format Formula of Google Sheets

Perhaps the best and easiest function to convert a date to any form in Google Sheets is by using the TEXT function.

TEXT(number, format)

text function syntax

The fundamental purpose of the TEXT function is to take a number and convert it to a text or string in a user-defined format. With the date being a number, this function is perfect for converting them into something else.

Note, however, that the TEXT function always has its results in text format. Meaning that the resultant date or any output will no longer be a numerical value. This means that numerical calculations like sorting and arithmetic will no longer be possible.

On the other hand, the TEXT function has a multitude of text codes used in the format field to format dates in any way the user requires. These are:

Day

  • D or DD: Returns the day number of the month. DD will always return two digits. E.g., 02 or 15.
  • DDD: Returns the shortened day name of the date. E.g., Mon or Tue.
  • DDDD: Returns the full name of the day. E.g., Monday or Tuesday

Month

  • M or MM: Returns the month number. MM will always return two digits. E.g., 06 or 11.
  • MMM: Returns the shortened month name of the date. E.g., Feb or Aug.
  • MMMM: Returns the full name of the month. E.g., February or August

Year

  • Y or YY: Returns the last two digits of the year. E.g., 05 or 15.
  • YYY or YYYY: Returns all four digits of the year. E.g., 2005 or 2015.

Learn More: How to Convert Date to String in Google Sheets (An Easy Guide)

That said, let’s see how we can use this function.

Convert Date to Month and Year Using a Formula

Here, we have a column of date values. As you can see, the values are right-aligned by default. This means that these are numerical values and are in the correct format.

list of dates

In the Month-Year column, we apply the following TEXT formula:

=TEXT(B3,"MM-YYY")

convert date to month and year in google sheets using the text function

What we have just shown is a typical example. We can take advantage of the function’s text codes to present different Month-Year outputs:

different formats we can convert date to year and month in google sheets using the text function

Convert Date to Month and Year With a Single Formula

It is possible to present all the converted dates at once instead of applying a formula in each row. We can do this by considering a cell range reference for our formula instead of a single cell reference.

Here, we will select the Date column for the number field of the TEXT function. The range reference is open-ended (B3:B) to include the entirety of the column. To really make this formula work, we must use the ARRAYFORMULA function to present all the data.

Our formula:

=ArrayFormula(TEXT(B3:B,"MMM-YYYY"))

covert a range of dates with only one formula

Since we used an open-ended range, the formula considers the blank cells in the column as well. To control and remedy this issue we have to include a condition with the IF function.

Our updated formula looks like this:

=ArrayFormula(IF(B3:B="",,TEXT(B3:B,"MMM-YYYY")))

updating the arrayformula with if condition to ignore blank cells

While the final formula looks big, remember that it only covers a single cell and also automatically and dynamically pulls data from the given range. This is a huge advantage when it comes to working with large datasets.

III. Using the EOMONTH Function to Convert Date to Month and Year in Google Sheets

As we mentioned before when introducing the TEXT function, the resultant conversion of date to month and year will no longer be numerical, barring it from being used in any number calculations.

But a promising alternative presents itself that will help us retain the date’s numerical value and also allow us to extract the month and year. This is with the help of the EOMONTH function.

EOMONTH(start_date, months)

eomonth function syntax

Combining this function with simple formatting learned in method 1 of this article, will allow us to convert a date to any desired format keeping its value intact.

To begin, we simply type in the following formula in cell C3:

=EOMONTH(B3,-1)+1

using eomonth function to extract the values of month and year correctly from a date

If you have used EOMONTH before, you should already know that this function returns the last date of the month by default. For 5/17/2013 it will be 5/31/2013.

With EOMONTH(B3,-1), the function returns the last date of the previous month. The result will be 4/30/2013.

Adding +1 to the last date of the previous month returns the first date of the current month. The result is 5/1/2013. That is the result you see in the image.

All that remains is to convert the resultant date to only show Month and Year. You can take any formatting approach for this. We have used the approach we have shown in method 1 of this article.

convert date to month and year in google sheets using the eomonth function

Why did we use this approach?

Firstly, the EOMONTH function does not change the base value of the date even after conversion, at least for the month and year values. It is still numerical and can be used in calculations, like sorting.

Secondly, we do not require the value of the day from our date for converting a date to month and year. So, leaving it at 1 has no consequences. Since the values of month and year are correctly extracted by EOMONTH, we simply need to convert them to our desired format ignoring the day.

Note that this method can also be used with ARRAYFORMULA and IF to present all values with a single function. The formula syntax is the same as the one with the TEXT function. Just remember to change the date format afterwards.

=ArrayFormula(IF(B3:B="",,EOMONTH(B3:B,-1)+1))

Final Words

That concludes our simple guide to converting a date to month and year in Google Sheets. Custom formatting can always be a go-to approach when it comes to only presenting certain date values. Whereas functions present the users with a creative touch to better customize their outputs.

Feel free to leave any queries or advice you might have for us in the comments section below.

Related Articles for Reading

Mehrab Imtiaz

Mehrab Imtiaz

Mehrab Imtiaz is a Technical Writer for officewheel.com specializing in everything Google Sheets. Mehrab has always had a profound passion for writing and with his experience with MS Excel and Google Sheets throughout his career, it seems to be a great match when it comes to dishing out tips and tutorials for all to enjoy.

We will be happy to hear your thoughts

Leave a reply

OfficeWheel
Logo