# 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.

• After selecting the desired pattern, your previously selected dataset will be formatted automatically.

Related Content: How to Convert Date to Month in Excel

## Practice Worksheet

Here I have provided a practice worksheet for you. You can experiment with it & learn the methods shown above.

## Related Articles

<< Go Back to Date Format | Number Format | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Asif Khan Pranto

Md. Asif Khan Pranto worked as an Excel and VBA Content Developer in Exceldemy for over two years and published some articles for the website. He is passionate about exploring new aspects of Excel and VBA. He received his Bachelor of Science in Mechanical Engineering from the Islamic University of Technology. Now, he is pursuing a Master of Development Studies to experience a new spectrum of knowledge. Apart from creating Excel tutorials, he is interested in Data Analysis... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF