Asif Khan Pranto
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.
- Now use AutoFill for the remaining Cells of the Year Column.
- 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.
- Now the above formula will return the Month & Year value having a separator.
- Now using the AutoFill feature we will have our Date Converted into Month & Year.
- 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.
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.
- Type the Following CONCAT formula.
=CONCAT(D5,"-",E5)- Put your desired separator in-between “ “ symbols.
- It will return the Month & Year value having a separator.
- Now using the AutoFill feature we will have our Converted Date into Month & Year.
- 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:
- How to Convert a Date to dd/mm/yyyy hh:mm:ss Format in Excel
- Get First Day of Month from Month Name in Excel (3 Ways)
- How to Get Last Day of Previous Month in Excel (3 Methods)
- Convert 7 Digit Julian Date to Calendar Date in Excel (3 Ways)
- How to Stop Excel from Auto Formatting Dates in CSV (3 Methods)
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.
- Then follow Home tab >> Format >> Format Cells.
- 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’.
- After selecting the desired pattern your previously selected dataset will be formatted Automatically.
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.
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.
