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

Get FREE Advanced Excel Exercises with Solutions!

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.

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)

"</p

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

"</p

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

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

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.

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.


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
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

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo