How to Convert Date to Month & Year in Excel & Google Sheets

This tutorial demonstrates how to convert dates to month and year in Excel and Google Sheets.

 

 

Number Formatting – Show Only Month and Year

You can display a date on the screen as just the month and year by using custom number formatting in Excel.

1. Highlight the cells you wish to format.

 

 

2. In the Ribbon, select Home > Number, and then click the down arrow for the Number format drop-down box.

 

 

3. Select More Number Formats…
   

 

 

OR
Select the small arrow in the bottom right corner of the Number group to open the number formatting dialog box.

 

 

4. Then, select Custom at the bottom of the list. In the Type box, enter mmmm yyyy. Finally, click OK to apply the format to the cells.

 

 

The cell value remains the same, but the number is displayed as November 2020 instead of 11/dd/2020.

 

 

Try mmm yyyy and mm yy to create more custom number formats.

 

 

TEXT Function

You can also use the TEXT Function to extract the month and year from a date.

 

 

1. Click in cell C3
2. Type in the following formula.

=TEXT(B3, "mm")

 

 

3. Click in cell D3.
4. Type in the following formula.

=TEXT(B3, "yyyy")

 

 

The TEXT formula returns the values to Excel as left-aligned text.

As with custom number formatting, you can use a number of variations of mm and yyyy – like mmm, mmmm, yy – to return the month and year in different formats.

MONTH and YEAR Functions

To extract the month and year from a date, you can use the MONTH and YEAR Functions.

 

 

1. Click in cell C3.
2. Type in the following formula.

=MONTH(B3)

 

 

The cell then shows only the relevant month.

 

 

3. Click in cell D3.
4. Type in the following formula.

=YEAR(B3)

 

 

The cell shows the relevant year.

The MONTH and YEAR Functions return the data to Excel right-aligned numbers.

Number Formatting in Google Sheets

You can also use custom number formatting in Google Sheets to display a date as mmmm yyyy.

1. Highlight the cells you wish to format.
2. Click on the 123 drop-down list on the toolbar in Google Sheets.
3. Select Custom date and time.

 

 

The format you currently have is shown in the format box. You can add and remove formats as needed, or select an appropriate format.

 

 

4. Click on the little arrows to the right of the Month box to select the month format you need.

 

 

5. Delete the Day format from the custom format box.

 

 

6. Click on the little arrows to the right of the Year box to select the correct format for the year.

 

 

7. Remove the extra separators between the month and year and make sure there is a space between the month and year formats.

 

 

8. Click Apply to apply your format to your worksheet.

 

 

Your selected cells are now formatted accordingly.

 

 

Try mmm yyyy and mm yy to create more custom number formats.

 

 

Month and YEAR Functions in Google Sheets

To extract the month and year from a date, you can use the MONTH and YEAR Functions.

 

 

1. Click in cell C3.
2. Type in the following formula.

=MONTH(B3)

 

 

The cell only shows the relevant month.

 

 

3. Click in cell D3.
4. Type in the following formula.

=YEAR(B3)

 

 

The cell shows the relevant year.