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:
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:
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