How to Convert a Date into a Month and Year in Excel (Formula)

Last Updated: November 21, 2023
puneet-gogia-excel-champs

- Written by Puneet

Excel has two ways to convert a date into a Month and year. And in this tutorial, we will learn both methods in detail.

Use Custom Formatting to Convert Date into a Month, Year, or Month and Year

You can use the below steps for this:

  1. First, select the cell where you have the date.
    convert-date-into-month-and-year
  2. After that, use the shortcut Ctrl + 1 to open the Format Cell option.
    open-format-cells-option
  3. From here, click on the “Custom” category.
    choose-custom-category
  4. Now, in the type input bar, you must specify the format to apply to the date. For example, you can enter “mmm-yyyy” for Month and Year.
    input-date-format-as-mmm-yyyy
  5. In the end, click OK to apply the format to the cell.
    apply-month-and-year-format

And when you click OK, it will convert the date into month and year only.

date-as-month-and-year-only

And if you see the formula bar, it’s still a date with day, month, and year. The only difference is the format you have applied to the cell to convert it into month and year.

Below are the few formats that you can use:

  • mmm – for the short month name.
  • mm – for the month number.
  • mmmm – for the long month name.
  • yy- for the two-digit year.
  • yyyy – for the four-digit year.

Convert into Month and Year into a Separate Cell

Let’s say you need to have the result in a separate cell; in this case, you can use the function TEXT. This function allows you to extract value from a date and then format it as you want.

convert-date-into-a-seperate-cell
=TEXT(A1,"mmm-yyyy")

With text function, you have two arguments to define.

In the first argument (value), specify the date you want to convert to a month and year or a month and a year separately. And in the second argument (format_text), you need to specify the format.

You can use the formats below, which are the same as those we used in the first methods.

  • mmm
  • mm
  • mmmm
  • yy
  • yyyy
  • mmm-yyyy
  • mm-yy

Get the Excel File