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

Suppose we have a dataset of several employees with DoB (date of birth) in column C. We want to convert the date to month and year only for our convenience.

Convert Date to Month and Year in Excel: 4 Ways

Method 1 – Convert Date to Month and Year in Excel Using Combined Functions & Ampersand

Steps:

Select cell D5.
Input the formula:

=MONTH(C5)

Press Enter to find the month value of cell C5.

Use the Fill Handle to AutoFill formula in the rest of the cells of the MONTH column.

In cell E5, type the following formula:

=YEAR(C5)

This will give us the year value of cell C5.

Use AutoFill for the remaining cells of the YEAR column.

Select cell F5 and type the formula:

=D5&”/”&E5

If you want to use any other separator like "-", then type that in the formula.

The above formula will return the month and year value having a separator.

Using the AutoFill feature, the entire dataset is converted into month and year.

If you want to delete any cell of columns C, D & E; you will lose the value in column F.
Copy the whole column.
Use the advanced Paste options to paste just the values.
This removes the formula, allowing you to use the values independently from the previous three columns.

Method 2 – Using Combined Functions to Convert Date to Month and Year in Excel

Steps:

Follow the steps from Method 1 to fill up the MONTH and YEAR columns.
Select cell F5.

Type the following CONCAT formula:

=CONCAT(D5,"-",E5)

Put your desired separator in between " " symbols.

Press Enter and it will return the month and year value having a separator.

Use the AutoFill feature to complete the column.

Method 3 – Convert Date to Month and Year in Excel with the TEXT Function

To use the TEXT function, we need to learn a few format codes for months and years.

Year Codes:

yy – two-digit visualization of the year (e.g. 99 or 02).
yyyy – four-digit visualization of the year (e.g. 1999 or 2002).

Month Codes:

m – one or two-digit visualization of the month (e.g. 5 or 11)
mm – two-digit visualization of the month (e.g. 05 or 11)
mmm – month visualization in three letters (e.g. May or Nov)
mmmm – month represented with the full name (e.g. May or November)

Steps:

Select cell D5.

Now type the following formula:

=TEXT(C5,"m/yy")

Here "/" is your desired separator in between " " symbols.

It will return the month and year value in the desired format.

Use AutoFill for the whole column.
You can repeat the process with a different formatting for the text.

Method 4 – Employing Number Formats to Convert Date to Month and Year in Excel

Steps:

Select the entire column C.

Go to the Home tab and in Format, select Format Cells.

Upon clicking Format Cells, a dialogue box will appear.
Go to Number tab and select Date.
Then scroll through the Type box & select your desired format.
Here I have selected the March-12 format which can be explained as the full name of the month with the last two digits of the year.