One common challenge that many Excel users encounter is extracting specific information from a date, such as the year.
Whether you’re a seasoned Excel expert or just starting your spreadsheet journey, understanding how to extract the year from a date can be incredibly useful.
In this post, we will walk you through different methods to extract a year from a date in Excel. Starting with the fundamental formula approach using built-in functions, to more advanced techniques involving custom formulas.
Example For Extracting Year From a Date
Let’s practice together by looking at the dataset. Our dataset contains Sample Transactions and their transaction date in multiple formats.
Let’s extract the years from the date using two formulae and one without any. So, we will be using the YEAR and TEXT Function along with a customized date format.
So, here’s how to extract year from a date in Excel:
1. Apply The YEAR Function
The built-in YEAR Function in Excel is used to extract the year from a given date and return the year as a four-digit number. So, even if your date format is let’s say 25-08-23, the returned result will be 2023.
Syntax of the YEAR Function:
Here, the serial number can be any cell number, or you can use a date value directly within the function. So, in our case, let’s use this function.
In cell G6, we are typing =YEAR( and as a cell reference we are using E6. So, our formula will be:
Press Enter and drag the formula to other cells. Note that, no matter what the format of the date is, Excel is returning the four-digit year number.
So, the YEAR function is easy to use, simple, time saving, and always shows the accurate answer.
The next method is a little bit complicated.
2. Use the TEXT Function
Notice that the formatting of the previous result was in a Date Format. But here, we will get the General format (that is in text). Both functions work the same way.
So, how to use the TEXT Function?
Type =TEXT(E6, “YYYY”) in the desired cell. Note that the cell number will change according to your worksheet. Then press Enter. The result will be a 4-digit text date. If you want to show only 2-numerical digits, then use =TEXT(E6,”YY”). Excel will use accurate results regardless of the date format you are using.
The reason behind Excel showing a result in a text format is because TEXT functions (Like LEFT, MID, RIGHT, and TEXT) are designed to manipulate and extract text from strings, not date values.
3. Utilize The Custom Formatting Options
In this case, you have to copy and paste the date to the cells that you want to format.
The next thing you need to do is select the range of cells and open the Formatting dialog box using Ctrl+1 or Cmd+1. Select the custom format and type yyyy. See the sample box as it changed from a random formatting to a desired one.
Now press OK and your year extraction from a date is complete. The best thing is, it works for any date format. Try it.
Conclusion
In this post, we covered how to extract year from dd/mm/yyyy format in excel, or any other formatting like dd-mm-yyyy. There are only three ways to do so. Using the YEAR, TEXT, and the Custom Formatting.
