Durjoy Paul
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.
- 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.
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.
Read More: Extract Text After a Character in Excel (6 Ways)
Similar Readings
- VBA Code to Convert Text File to Excel (7 Methods)
- How to Import Text File with Multiple Delimiters into Excel (3 Methods)
- How to Convert Text File to Excel Automatically (3 Suitable Ways)
- Transfer Data from One Excel Worksheet to Another Automatically with VLOOKUP
- How to Import Data from Secure Website to Excel (With Quick Steps)
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’.
- This will modify all the selected cells and provide year only.
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
- Transfer Data from One Excel Worksheet to Another Automatically
- How to Pull Data From Another Sheet Based on Criteria in Excel
- Extract Data from One Sheet to Another Using VBA in Excel (3 Methods)
- How to Pull Data from Multiple Worksheets in Excel VBA
- How to Extract Data from Excel to Word (4 Ways)
- Import Data from Excel into Word Automatically Using VBA (2 Ways)
