How to Convert Date to Year in Excel (3 Quick Ways)

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.

3 Quick Ways to Convert Date to Year in Excel

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)

Convert Date to Year Using Excel YEAR Function

  • As a result, we will get the following result. Use the Fill Handle (+) tool to get the year from the rest of the dates.

Convert Date to Year Using Excel YEAR Function

  • 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")

Excel TEXT Function to Extract Year from a Date

  • 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:


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.

Use ‘Format Cells’ Option to Convert Date to Year in Excel 

  • 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.

Use ‘Format Cells’ Option to Convert Date to Year in Excel 

  • Finally, upon clicking OK, all the selected dates are converted to years.

Use ‘Format Cells’ Option to Convert Date to Year in Excel 

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.


Related Articles

Hosne Ara

Hosne Ara

Hi, This is Hosne Ara. Currently, I do write for ExcelDemy. I have a long experience working with different industries and I have seen how vast the scope of Microsoft Excel is. So, eventually, I started to write articles on Excel and VBA. Basically, my articles are targeted to help people who are working in Excel. By profession, I am an Engineer. Materials and Metallurgical Engineering is my major. Besides, I am a certified Project Manager (PMP) too. I have worked with Power Plant and IT industry earlier. As a person, I am detail-oriented and love doing research. Establishing a greener world is one of my mottos.

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