Using Excel’s Text Formula to Extract the Year from a Date

August 08, 2022

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!

The formula to extract the year from a date in Excel is the following:

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

sample data set we will use to extract the year from using Excel's Text formula
Sample data set

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.

sample data set we will use to extract the year from using Excel's Text formula; table prepped with new column for us to write our formula into
Prepped data table

Using our year extract formula, we can type out the syntax as follows:

Setting up the TEXT formula in Excel
Setting up the TEXT formula in Excel

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:

A successfully executed Text formula in Excel

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:

An example of Excel's text formula for extracting the year from a dateo

That’s all there is to it, we’ve successfully extracted the year from a date field!

Why would you want to grab 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.

Zoom-in and Zoom-out of your Data

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!


Book a call to explore the different avenues the Analytic Iron team can help You extract insights from Your Data.
BOOK a Discovery cALL