How to Convert Date to Month and Year in Excel (3 Easy Ways)

Whenever you enter a date in a cell, Excel automatically recognizes the format and converts the cell to a date cell.

So, Excel knows which part of the date you entered is the month, which is the year and which is the day.

This can be quite helpful in many ways. One particular benefit of this capability of Excel is that it lets you display the date in any format you want. It even lets you extract parts of the date that you need.

For example, you might find the day part of the date irrelevant and just need to display the month and year.

Since Excel already understands your date, you can easily extract just the month and year and display it in any format you like.

In this tutorial, we are going to see three ways in which you can convert date to month and year in Excel.

Table of Contents

The Sample Data

Throughout this tutorial, we are going to be using the following set of dates. We will be converting these dates to month and year in Excel:

When working with dates, first and foremost, it is important to recognize the original format your Excel dates are in. For example, in the US format, dates usually begin with the month and end with the year (mm/dd/yyyy).

In the UK and other countries, dates begin with the day and end with the year (dd/mm/yyyyy). In still other places like China, Iran, and Korea, the order is completely flipped (yyyy/mm/dd).

Depending on your computer’s date settings, Excel will treat parts of your date differently.

So when entering the date, make sure you check the format and enter the date in the correct order. You don’t want the date 2/10 to be treated as October the 2nd, instead of February the 10th!

Also read: How to Convert Date to Quarter in Excel

Convert Date to Month and Year using the MONTH and YEAR function

The MONTH and YEAR functions can help you extract just the month or year respectively from a date cell. In order for this method to work, the original date (on which you want to operate) must be a valid Excel date. If not, then both these functions will return a #VALUE error.

Let’s see how you can extract the month from our sample data:

Click on a blank cell where you want the month to be displayed (B2)
Type: =MONTH, followed by an opening bracket (.
Click on the first cell containing the original date (A2).
Add a closing bracket )
Press the Return key.
This should display the month of the year corresponding to the original date. Copy this to the rest of the cells in the column by dragging down the fill handle or double-clicking on it.

You will see column B populated by the month of the year for all the dates of column A.

Now let’s see how you can extract the year from the same dataset:

Click on a blank cell where you want the year to be displayed (C2)
Type: =YEAR, followed by an opening bracket (.
Click on the first cell containing the original date (A2).
Add a closing bracket )
Press the Return key.
This should display the year corresponding to the original date. Copy this to the rest of the cells in the column by dragging down the fill handle or double-clicking on it.

You will see column C populated by the year corresponding to all the dates of column A.

Now let’s see how you can combine the results of the two to display both month and year in a nice format.

Let us say you want to display both month and year as “2-2018” for the date “02/10/2018”, and want to follow this pattern for all the dates.

Click on a blank cell where you want the new date format to be displayed (D2)
Type the formula: =B2 & “-“ & C2. Alternatively, you can type: =MONTH(A2) & “-” & YEAR(A2).
Press the Return key.
This should display the original date in our required format. Copy this to the rest of the cells in the column by dragging down the fill handle or double-clicking on it.

Now all your cells in column D2 have the new format:

Alternatively, if you want to display your month and year as 2/2018 instead, you only need to replace the “-“ in-between with a “/”. In this way, you can display the month and year in any format that you like.

Finally, if you want to just keep the converted values and want to remove the original dates and any intermediate columns that you created you need to first convert the formula results into constant values.

For this, copy the cells of column D and paste them as values in the same column (Right-click and select Paste Options->Values from the Popup menu). Now you can go ahead and delete columns A to C. You will be left with only the converted values that have the month and year.

Although this is quite an easy and intuitive way to convert dates to months and years, it is a less popular method. This is because this method does not provide a lot of flexibility compared to the other two methods we will show next.

Also read: How to Calculate the Number of Months Between Two Dates in Excel?

Convert Date to Month and Year using the TEXT Function

The TEXT function in Excel converts any numeric value (like date, time, and currency) into text with a specified format.

The syntax of the TEXT function is:

= TEXT (value, format_code)

Here,

value is the numeric value or reference to the cell that you want to convert
format_code is the format you want to convert the cell into

In the above example, the TEXT function applies the format_code that you specified on the value and returns a text string with that format. For example, if you have a date “2/10/2018” in cell A2, then =TEXT(A2, “mm/yyyy”) will return “02/2018”

There are a number of format codes that you can use. We have enlisted below the basic building blocks for the format codes:

Format Codes for Year:

You can use the following two basic format codes to represent year values:

yy – two-digit representation of year (e.g. 20 or 12)
yyyy – four-digit representation of year (e.g. 2020 or 2012)

So, if you apply =TEXT(A2, yy) in our example dataset, it will return “18”.

If you apply =TEXT(A2, yyyy), then it will return “2018”.

Format Codes for Month of the Year:

You can use the following four basic format codes to represent month values:

m – one or two-digit representation of the month (eg; 8 or 12)
mm – two-digit representation of the month (eg; 08 or 12)
mmm – month abbreviated in three letters (eg: Aug or Dec)
mmmm – month expressed with the full name (eg: August or December)

So, if you apply =TEXT(A2, m) in our example dataset, it will return “2”.

If you apply =TEXT(A2, mm), then it will return “02”.
If you apply =TEXT(A2, mmm), then it will return “Feb”.
If you apply =TEXT(A2, mmmm), then it will return “February”.

Let us see how we can apply the TEXT function to our sample dataset to convert all the dates to different formats.

We will first see how to convert the dates in column A to the format shown in column B in the image below:

Below are the steps to change the date format and only get month and year using the TEXT function:

Click on a blank cell where you want the new date format to be displayed (B2)
Type the formula:
```
=TEXT(A2,”m/yy”)
```
Press the Return key.
This should display the original date in our required format. Copy this to the rest of the cells in the column by dragging down the fill handle or double-clicking on it.
Copy this column’s formula results by pressing CTRL+C or Cmd+C (if you’re on a Mac).
Right-click on the column and form the popup menu that appears, press Paste Values from the Paste Options.
This will store the formula results as permanent values in the same column. Now you can go ahead and remove column A if you want to.

The format code that you put in the formula (at step 2) will vary according to the format you want your month and year to appear in. Here are the format codes along with the type of result you will get when applied to cell A2:

Function	Result
=TEXT(A2, “mm/yy”)	02/18
=TEXT(A2, “mm-yy”)	02-18
=TEXT(A2, “mm-yyyy”)	02-2018
=TEXT(A2, “mmm, yyyy”)	Feb, 2018
=TEXT(A2, “mmmm, yyyy”)	February, 2018

So you see, you can use the TEXT function to convert your dates to any format of your choice. All you need to do is change the format code according to your requirement.

Note: Using this formula, your date gets converted to a text format. If you want to convert it back to a date format, you need to use the Format Cells feature.

Also read: How to Convert Text to Date in Excel?

Convert Date to Month and Year using Number Formatting

Excel’s Format Cell feature is a versatile one that lets you perform different types of formatting through a single dialog box.

Here’s how you can convert the dates in our sample dataset to different formats.

Select all the cells containing the dates that you want to convert (A2:A6).
Right-click on your selection and select Format Cells from the popup menu that appears. Alternatively, you can select the dialog box launcher in the Number group under the Home tab.
This will open the Format Cells dialog box. Click on the Number tab
Under Category on the left side of the box, select the Date option.
This will display a number of formatting options for date on the right side.
Select the format that you want. For example, if you want to display the first date in the format “Feb-18”, then select the matching format option.
If you don’t find an option for the format you want to use, then you can use the Custom option from the Category list on the left. This lets you convert the cell to a custom format.
Look if your format is available among the date format codes under Type. If not, you can type in your format code in the input box just below Type. So, if you want to display the first month in the format “2/18”, then type “m/yy”.
Click OK to close the Format Cells dialog box.

All your selected cells should now be formatted to your required format.

This method differs from the previous one (using the TEXT function) in four ways:

With this method, you can perform the operation directly on the original cells.
You can get your conversion done in one go. So you don’t need to have a separate cell to enter the formula, then paste by value and then delete the original cells (as you would need to if using the TEXT function).
This method changes just the format of the original date, the underlying date, however, remains the same. So if you want to later recover the original date value (along with the day), you can easily access it. With the TEXT function method, however, the original date value is lost because the conversion changes the entire value of the date.
The results you get from using this method are of type Date, rather than Text, so you can perform date operations on them directly without having to convert them.

These were three ways in which you can convert date to month and year in Excel. Using these you can convert your date to any format you need.

We hope you found our methods useful and that you will apply it to your own Excel data.

Other Excel tutorials you may like:

How to Convert Date to Month and Year in Excel