See all How-To Articles

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.

 

convertdate intro

 

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.

 

convertdate mmmmyyyy

 

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

 

convertdate ribbon

 

  1. Select More Number Formats…

 

convertdate more number formats

 

OR

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

 

convertdate number format

 

  1. 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.

 

convertdate format cells

 

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

 

convertdate formatted

 

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

 

convertdate mmyy

 

TEXT Function

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

 

convertdate text

 

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

 

convertdate text formula

 

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

 

convertdate text formula 2

 

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.

 

convertdate year

 

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

 

convertdate month

 

The cell then shows only the relevant month.

 

convertdate month formula

 

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

 

convertdate year formula

 

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.

 

convertdate gs menu

 

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

 

convertdate gs custom

 

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

 

convertdate gs month

 

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

 

convertdate gs delete

 

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

 

convertdate gs year

 

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

 

convertdate gs remove separator

 

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

 

convertdate gs apply

 

Your selected cells are now formatted accordingly.

 

convert date to month year 23

 

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

 

convertdate gs results

 

Month and YEAR Functions in Google Sheets

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

 

convertdate gs formula month

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

 

convertdate gs month formula

 

The cell only shows the relevant month.

 

convertdate gs show month

 

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

 

convert gs year formula

 

The cell shows the relevant year.

 

convertdate gs show year