In this article, we will learn how to convert date to month and year in Excel. Sometimes we need to eradicate the day count from the date & use only month and year for visual convenience. Reading this we will learn how to do so using a few formulas and format features.
Suppose we have a dataset of several employees with DoB in column C. Now we want to convert the date having month and year only for our convenience. Now I’ll show you how to do this in Excel.
Convert Date to Month and Year in Excel: 4 Ways
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 and 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 a 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 use the Fill Handle to AutoFill formula in the rest of the cells of the MONTH column.
- Now we will separate the year from the 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.
- Join the month and date of row 5. We will use the Ampersand (&) symbol.
- Select cell F5 and type the formula.
=D5&”/”&E5- If you want to use any other separator like
"-", then type"-"in the formula.
- Now the above formula will return the month and year value having a separator.
- Now using the AutoFill feature we will have our data 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.
- So keep the value of column F intact first copy the whole column.
- Then use the Paste options 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
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 and 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 the MONTH and YEAR columns.
- Type the following CONCAT formula.
=CONCAT(D5,"-",E5)- Put your desired separator in between
" "symbols.
- It will return the month and year value having a separator.
- Now using the AutoFill feature we will have our converted date into month and year.
- Now if you want to delete the MONTH and YEAR column & keep column MONTH-YEAR only, follow the procedures shown in Method 1.
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 and year in Excel using the TEXT function.
Steps:
- To use the TEXT function, we need to learn a few format codes for months and years.
- In Excel, we can use the following basic format Codes to indicate year and 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 (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)
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 your desired separator in between" "symbols.
- It will return the month and 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: How to Convert Date to Text YYYYMMDD
Method 4. Employing Number Formats to Convert Date to Month and Year in Excel
In this method, we will learn how to convert dates into months and years 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 the Home tab >> Format >> Format Cells.
- Upon clicking Format Cells, 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 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.
Download Practice Workbook
Conclusion
Reading the article above, we will easily learn how to convert date to month and 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
- How to Convert Date to Year in Excel
- How to Convert Date to Number in Excel
- How to Convert Date to Quarter and Year in Excel
- How to Convert Date to Day of Week in Excel
- How to Convert Date to Day in Excel
<< Go Back to Change Date Format | Date Format | Number Format | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
