Hosne Ara
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 need. For instance, I have a date of 22-Feb-2021. Now 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 how you can turn dates into years.
Download Practice Workbook
You can download the practice workbook that we have used to prepare this article.
3 Quick Ways to Convert Date to Year in Excel
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 (4 Methods)
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: Excel Formula for Current Month and Year (3 Examples)
Similar Readings:
- How to Convert Date to Month in Excel (6 Easy Methods)
- VBA to Remove Time from Date in Excel (3 Methods)
- Get the First Day of the Current Month in Excel (3 Methods)
- How to Convert a Date to dd/mm/yyyy hh:mm:ss Format in Excel
- Convert 7 Digit Julian Date to Calendar Date in Excel (3 Ways)
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 (see screenshot).
Read More: How to Convert Date to Month and Year in Excel (4 Ways)
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.
