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.

How to Convert Date to Month and Year in Excel

  • Use AutoFill for the remaining cells of the YEAR column.

How to Convert Date to Month and Year in Excel

  • Select cell F5 and type the formula:
=D5&”/”&E5

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

How to Convert Date to Month and Year in Excel

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

How to Convert Date to Month and Year in Excel

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

How to Convert Date to Month and Year in Excel

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

How to Convert Date to Month and Year in Excel

Read More: How to Convert Date to Day of Year in Excel


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.

How to Convert Date to Month and Year in Excel

  • Type the following CONCAT formula:
=CONCAT(D5,"-",E5)

Put your desired separator in between " " symbols.

How to Convert Date to Month and Year in Excel

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

How to Convert Date to Month and Year in Excel

  • Use the AutoFill feature to complete the column.

How to Convert Date to Month and Year in Excel


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.

How to Convert Date to Month and Year in Excel

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

How to Convert Date to Month and Year in Excel

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

How to Convert Date to Month and Year in Excel

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

How to Convert Date to Month and Year in Excel

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.

How to Convert Date to Month and Year in Excel


Download Practice Workbook


Related Articles


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

Get FREE Advanced Excel Exercises with Solutions!
Asif Khan Pranto
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

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo