Hey Data friends! Today we will learn how to extract the year from a date field using the TEXT function in Excel. First, we’ll outline the TEXT formula needed to pull out the year from a date in Excel, and afterward, we’ll go over why you might want to do this type of transformation on your data.
A quick side note, extracting the year from a date field is also possible using the YEAR formula. The YEAR formula is much more straightforward to use than the TEXT formula, but the downside is that it’s not as flexible as the TEXT formula. If you want to check out that tutorial then head over to the Year formula tutorial.
Let’s get started!
=Text( DateField , “YYYY”)
Where I have DateField, you’d set the cell of the date you need to pull the year from. Let’s walk through an example so we can see the formula in action.
The sample data we will be working with is below:
This is the same data from our LINK TO EXCEL CALC DATE DIFFERENCE tutorial. In that tutorial, we calculated the date difference between the Course Start Date and the Course End Date. Today, we will use this data to extract the year from the Course Start Date field.
I added a column where we will type our formula TEXT formula.
%20-%20Data%202.png)
Using our year extract formula, we can type out the syntax as follows:
%20-%20Formula%201.png)
What we’re telling Excel with this TEXT formula is that we want it to look at the Course Start Date Field (cell C5) and we want it to extract the “YYYY” where “YYYY” is interpreted as the year by Excel. The result then looks like the below:
%20-%20Output%201.png)
We can run this formula down the table to see it in action. I changed some of the years around (the cells in pink) from the original sample table since all years were originally 2022 and that doesn’t make for a fun example of the TEXT formula at work. The output looks like the below:
%20-%20Output%202.png)
That’s all there is to it, we’ve successfully extracted the year from a date field!
The biggest reason you’d want to extract the year from a date in Excel is so that you can aggregate your data to different levels.
Let’s say you’re doing an exploratory data analysis and the data set you’re working with is daily transactional data. While it is important to look at the data at a daily level – you can get an understanding of intra-day fluctuations and peaks – there is also value in aggregating your data to higher, more macro levels. If you aggregate your daily data up to a yearly level, you can then look at the year-over-year performance and get a high-level look at the trend and performance over the long run. The ability to “zoom-in” and “zoom-out” of your data gives you the power to look at the data from different angles, this is valuable when working to strengthen your understanding of the underlying data.
As always, if you have any questions or need anything clarified you can reach me through my DMs on Twitter @Cest_Nick. I hope you enjoyed this post, I’ll catch you on the next one!
-Nick
