Excel: How to Convert Date to Month and Year Format


You can use one of the following formulas to convert a date to a month and year in Excel:

Formula 1: Convert Date to Month and Full Year (e.g. 01/2022)

=TEXT(A1, "mm/yyyy")

Formula 2: Convert Date to Month and Last Two Digits of Year (e.g. 01/22)

=TEXT(A1, "mm/yy")

Formula 3: Convert Date to Abbreviated Month and Full Year (e.g. Jan. 2022)

=TEXT(A1, "mmm. yyyy")

Formula 4: Convert Date to Full Month and Full Year (e.g. January 2022)

=TEXT(A1, "mmmm yyyy")

Note that each formula assumes the date is in cell A1.

The following examples show how to use each formula in practice.

Example 1: Convert Date to Month and Full Year

We can use the following formula to convert a date to a month and full year:

=TEXT(A2, "mm/yyyy") 

We can type this formula into cell B2 and drag the formula down to every remaining cell in column B:

Excel convert date to month and year

Column B displays the month and full year for each date in column A.

Example 2: Convert Date to Month and Last Two Digits of Year

We can use the following formula to convert a date to a month and the last two digits of the year:

=TEXT(A2, "mm/yy") 

We can type this formula into cell B2 and drag the formula down to every remaining cell in column B:

Column B displays the month and last two digits of the year for each date in column A.

Example 3: Convert Date to Abbreviated Month and Full Year

We can use the following formula to convert a date to an abbreviated month name and the full year:

=TEXT(A2, "mmm. yyyy") 

We can type this formula into cell B2 and drag the formula down to every remaining cell in column B:

Column B displays the abbreviated month and full year for each date in column A.

Example 4: Convert Date to Full Month and Full Year

We can use the following formula to convert a date to a full month name and the full year:

=TEXT(A2, "mmmm yyyy") 

We can type this formula into cell B2 and drag the formula down to every remaining cell in column B:

Excel convert date to month name and year

Column B displays the full month and full year for each date in column A.

Additional Resources

The following tutorials explain how to perform other common tasks in Excel:

How to Convert Date to Quarter and Year in Excel
How to Compare Dates Without Time in Excel
How to Filter Dates by Month in Excel

Leave a Reply

Your email address will not be published.