When you enter a date in Microsoft Excel, Excel automatically recognizes the inserted data and stores it as a date. Besides, you can change the date format too. Whatever the date format is, Excel recognizes different parts of the date (day, month, year, etc.). As Excel understands dates, you can extract part of the date if you want. For instance, I have a date of 22-Feb-2021. I will extract the year portion (2021) from the date using Excel functions. Similarly, you can convert a date to a year by changing the format of the Excel cell. This article will guide you to turn dates into years.
Convert Date to Year in Excel: 3 Quick Ways
Let’s consider a list of dates as below. I will convert these dates to years using different options.
1. Convert Date to Year Using Excel YEAR Function
You can extract a year from a date by using the YEAR function in Excel. The YEAR function returns the year of a date, an integer in the range 1900 – 9999.
Steps:
- Type the below formula in cell C5 and press Enter from the keyboard.
=YEAR(B5)
- As a result, we will get the following result. Use the Fill Handle (+) tool to get the year from the rest of the dates.
- Consequently, we get the year part from all the dates.
Read More: How to Convert Date to Day of Year in Excel
2. Excel TEXT Function to Extract Year from a Date
This time, I will use the TEXT function to convert a date into a year. Follow the below steps to do the task.
Steps:
- Type the following formula in cell C5 and hit Enter.
=TEXT(B5, "yyyy")
- Once you enter the formula, Excel will return the year of the corresponding date. Similar to the previous method, use the Fill Handle to extract the year part from all dates.
Read More: How to Convert Date to Day of Week in Excel
3. Use Format Cells Option to Convert Date to Year in Excel
Now I will use the Format Cells option to convert a date to a year. Let’s follow the below steps to perform the task.
Steps:
- First, select the entire dataset that contains dates (here B5:B10). Next, right-click on the selection and click on the Format Cells option.
- As a consequence, the Format Cells dialog box appears.
- Go to the Number tab, click on the Custom category, write yyy in the Type field, and press OK.
- Finally, upon clicking OK, all the selected dates are converted to years.
⏩ Note:
You can bring the Format Cells dialog by following the path: Home > Number group. Then click on the More icon.
Read More: How to Convert Date to Month and Year in Excel
Download Practice Workbook
You can download the practice workbook that we have used to prepare this article.
Conclusion
In the above article, I have tried to discuss several easy and quick ways to convert date to year in Excel elaborately. Hopefully, these methods and explanations will be enough to solve your problems. Please let me know if you have any queries.
