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

In this article, we will learn how to Convert Date to Month & Year in Excel. Sometimes we need to eradicate the day count from the Date & use only Month & Year for visual convenience. Reading this we will learn how to do so using a few formulas & Format features.

Suppose we have a dataset of several employees with DoB in Column C. Now we want to Convert the Date having Month & Year only for our convenience. Now I’ll show you how to do this in Excel.


Download Practice Workbook


4 Easy Ways to Convert Date to Month and Year in Excel

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

In this method, I’ll show you how to Convert Date to Month & Year in Excel using the MONTH, and YEAR Functions, and Ampersand (&).

Steps:

  • First, we have to select a Cell where we will separate the Month using the Month Formula.
  • I have selected Cell D5 where I will separate the Month value of Cell C5.
  • Now type the formula.
=MONTH(C5)

  • Upon pressing ENTER we will find 5 in Cell D5 which is the Month value of Cell C5.

  • Now using the Fill Handle I will AutoFill the rest of the Cells of the MONTH Column.

  • Now we will separate the Year from Date using the YEAR Function.
  • In Cell E5 I want to have the Year value of Cell C5.
  • I will type the following formula here.
=YEAR(C5)

  • This will give us the Year value of Cell C5.

How to Convert Date to Month and Year in Excel

  • Now use AutoFill for the remaining Cells of the Year Column.

How to Convert Date to Month and Year in Excel

  • Now to join Month & Date of Row 5 we will use the Ampersand (&) symbol.
  • Selectin Cell F5 I have typed the formula.
=D5&”/”&E5
  • If want to use any other separator like ‘-’, Then type “-” instead of in the formula.

How to Convert Date to Month and Year in Excel

  • Now the above formula will return the Month & Year value having a separator.

How to Convert Date to Month and Year in Excel

  • Now using the AutoFill feature we will have our Date Converted into Month & Year.

How to Convert Date to Month and Year in Excel

  • If you Delete any Cell of Column C, D & E; you will lose the value in Column F.
  • So keep the value of Column F intact first Copy the whole Column.
  • Then use the Paste Values option on the same Column Right-clicking the Mouse.
  • Thus we can Delete other Columns & have the Date Converted into the MONTH-YEAR Column.

How to Convert Date to Month and Year in Excel

Read More: How to Convert Date to Day of Year in Excel (4 Methods)


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

In this part of the article, we will learn how to Convert Date to Month & Year in Excel using the MONTH, YEAR & CONCAT Functions.

Steps:

  • Follow the Steps from method 1 to Fill Up the MONTH & YEAR Column.
  • Now select Cell F5 where you want to apply the CONCAT formula to join MONTH & YEAR Column.

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

  • It will return the Month & Year value having a separator.

How to Convert Date to Month and Year in Excel

  • Now using the AutoFill feature we will have our Converted Date into Month & Year.

How to Convert Date to Month and Year in Excel

  • Now if you want to Delete the MONTH & YEAR Column & keep Column MONTH-YEAR only, follow the procedures shown in Method 1.

Read More: Excel Formula for Current Month and Year (3 Examples)


Similar Readings:


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

In this method, I will show you how to Convert Date to Month & Year in Excel using the TEXT Function.

Steps:

  • To use TEXT Function first we need to learn a few format Codes for Months & Years.
  • In Excel, we can use the following basic format Codes to indicate Year & Month.

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 (eg; 5 or 11)
  • mm – two-digit visualization of the month (eg; 05 or 11)
  • mmm – month visualization in three letters (eg: May or Nov)
  • mmmm – month represented with the full name (eg: May or November)

Let’s select a Cell initially where we want to format the Date of Cell C5 to “m/yy” format using the TEXT formula.

  • I have selected Cell D5.

  • Now type the following formula.
=TEXT(C5,"m/yy")
  • Here “/” is the Use your desired separator in-between “ “ symbols.

  • It will return the Month & Year value in the desired format.

  • Now use AutoFill for the whole Column.
  • Then typing the Text formula using the suitable Code mentioned above we will get Month & Year Converted to our desired format.

Read More: Get the First Day of the Current Month in Excel (3 Methods)


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

In this method, we will learn how to Convert Date into Month & Year in Excel using the Number Formatting feature.

Steps:

  • Initially select the Cell or Cells where you want to format your Date.
  • I have selected Dates from my dataset which is in Column C.

How to Convert Date to Month and Year in Excel

  • Then follow Home tab >> Format >> Format Cells.

How to Convert Date to Month and Year in Excel

  • Upon clicking Format Cells then a dialogue box will appear.
  • Now follow Number >> Date.
  • Then scroll through the Type box & select your desired format.
  • Here I have selected the ‘March-12’ format which can be explained as ‘Full name of the Month-Last two digits of 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 Day of Week in Excel (8 Methods)


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


Conclusion

Reading the article above, we will easily learn how to Convert Date to Month & Year in Excel & those easy methods will make your dataset look comfortable & ease your job. Hope you have enjoyed reading this article. If you have queries please feel free to leave a comment.


Related Articles

Asif Khan Pranto

Asif Khan Pranto

Hello! I'm Asif here, currently working with Exceldemy as an Excel & VBA Content Developer. I write articles on Exceldemy about various ways to get out of Microsoft Excel's stuck conditions. My goal is to work with an organization which will give myself a chance to upgrade besides having a real impact on our surroundings. I'm passionate about travelling new communities & trekking. In my leisure period I usually read books. I've completed graduation in Mechanical Engineering & now I am pursuing Master of Development Studies to experience a new spectrum of knowledge. I’ve always been interested in research and development. So, here I will be posting articles related to Microsoft Excel. Hoped this may help you. Thank you.

We will be happy to hear your thoughts

Leave a reply

5 Excel Hacks You Never Knew

Genius tips to help you unlock Excel's hidden features

FREE EMAIL BONUS

ExcelDemy
Logo