How to Extract Year from Date in Excel (3 Ways)

When you have a large set of data with dates and you want to extract only years from data, Excel gives you every opportunity to do it. Mind it, you need to enter a recognizable date format in Excel then it is fairly easy to extract the year from the date in Excel. This article will provide you with a total overview of extracting years from data.


Download Practice Workbook

Download this practice workbook


3 Ways to Extract Year from Date in Excel

When it comes to extracting years from date in excel, we discuss 3 different methods to solve it. Here, two of them are by using Excel formulas and the other one is by using format cells in Excel. All 3 methods are very fruitful and easy to use. To show all these methods, we take a dataset that includes player name and their date of birth. We want to extract the year from their date of birth.


1. Extract Year from Date Using Year Function

Firstly, the most common and easy method to extract years from date is using the Year function. This function is not only popular but also very user-friendly. To use this method effortlessly, follow our steps carefully.

📌 Steps

  • Primarily, select cell D5 where you want to put the extracted year values.
  • Write down the following formula in the formula box:
=YEAR(C5)

Here, we put cell ‘C5’ because we want to extract the year from this particular cell. Then, press ‘Enter’. It’ll automatically show the year value.

How to Extract Year from Date in Excel

  • Now, drag the Fill Handle icon to the last cell where you want to put the extracted year. Here we have all the extracted year values.

How to Extract Year from Date in Excel

Read More: How to Extract Month from Date in Excel (5 Quick Ways)


2. Using Text Function to Extract Year

Our next method for extracting year from date is by using the Text Function. Text function defines as a function that converts values into format text through formatting codes. To use this function spontaneously, you need to follow our steps and have a good understanding of how it converts values to format text.

📌 Steps

  • Just like the previous function, select cell ‘D5’ where you want to put the extracted year value.
  • Write down the following formula in the formula box.
=TEXT(C5,”yyyy”)

Here, ‘C5’ denotes the cell value, and ‘yyyy’ denotes the ‘format text’. As we want to convert into year, that is why we put this ‘yyyy’.

  • Press ‘Enter’ and it’ll show the required year value. Then, drag the Fill Handle icon to the last cell where you want to extract the year value for the respective cells.

How to Extract Year from Date in Excel

Read More: Extract Text After a Character in Excel (6 Ways)


Similar Readings


3. Using Format Cells to Extract Year from Date

Last but not least, we can extract year from date by using Format Cells. If you enter any readable date, it’ll automatically appear in the date format. Now, to extract years from these readable dates, you need to customize the  Format Cells. Excel has 4 different ways to open  Format Cells.

Keyboard Shortcut:

Press the ‘Ctrl + 1’ button, and the Format cells box will pop up.

Format Cells Option:

Select the text from which you want to extract years and right-click on the selected text cell, several options will appear from which Format cells need to be selected.

From Home Tab:

Select the ‘Home’ tab in the ribbon, in the  ‘Home’ tab there is a Cells section from which the Format option needs to be selected.

From the Number section:

Select the Home tab in the ribbon, in the ‘Home’ tab there is a Number section from which the Format option needs to be selected

📌 Steps

  • If you want to keep all the data, then select all the data from which you want to extract the year and copy it to another column, and do the required formatting. Here, we copy the range of cells C5:C10  and paste it into the range of cells D5:D10.

  • Now, use any of the preferred ways to open Format Cells. We did it through the Number section from the Home In the Number section, there is a small arrow at the bottom. Click on it and the Format Cells window will pop up.

  • In the Format Cells window, first, select the Number In the section, you’ll get options like categories and types.

  • In the Category section, select Custom and change the type to ‘yyyy’. After that click on ‘OK’.

How to Extract Year from Data in Excel

  • This will modify all the selected cells and provide year only.

How to Extract Year from Date in Excel

Read More: How to Extract Data from Cell in Excel (5 Methods)


Conclusion

We have discussed 3 methods to extract the year from the date in Excel. As you can see all the methods are very easy to use and it takes no time to change from a given date to a year.If you have any questions feel free to ask in the comment section and don’t forget to visit our Exceldemy page.


Related Articles

Durjoy Paul

Durjoy Paul

Hi there! I'm Durjoy. I have completed my graduation from the Bangladesh University of Engineering and Technology. I am working and doing research on Microsoft Excel and here I will be posting articles related to it.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo