How-To

How to Extract the Year from a Date in Excel

By Jeff Butts

Updated October 30, 2023

Through the powerful features of Excel, learn how you can extract just the year from a date or series of dates.

Excel is a powerful productivity tool, letting you easily calculate just about any sort of data analysis you need. You can use it for budgeting, for creating timetables, or even as a contacts list complete with dates of birth.

What if you have many dates that you want to pull the years out of, though? With as powerful as Excel is, from performing simple calculations using formulas to generating helpful pie charts, it should come as no surprise that you can split the date apart and leave just the year. In fact, there are a few ways to do that, so let’s look at three ways to extract the year from a date in Excel.

How to Extract the Year from a Date in Excel Using the YEAR Function

First, you can extract the year from a date using a built-in function called YEAR(). Here’s how that works.

1. In a blank cell in your spreadsheet, type =YEAR(A1). Replace A1 with the cell containing the entire date.
   
2. Like magic, Excel fills in the cell with just the year from the selected date.
   
3. If you have multiple cells you want to extract the date from, you can click the autofill handle of the cell and drag it down to include all of the cells you need to populate with years.

Using the TEXT Function to Extract the Year

Next, you can use the TEXT() function to pull out just the year.

1. In an empty cell, type =TEXT(A1,”YYYY”). Again, replace A1 with the appropriate cell.
   
2. If you only want a two-digit year, change the function to =TEXT(A1,”YY”) instead.
   
3. As before, you can use the autofill handle to populate your formula to multiple cells.

Extracting the Year from a Date Using Data Features

Excel also includes a powerful tool that can split text in a cell into different columns. Using that feature, you can split your date apart into month, date, and year. Here’s how that works.

1. Click the top of the column containing the dates.
2. Next, click the Data ribbon.
3. Click the Text to Columns button.
   
4. In the next dialog box, make sure Delimted is selected, then click Next.
   
5. Deselect Tab, and select Other.
6. In the text box, enter the character separating the different parts of the date. In my example, it’s a slash (/).
   
7. The preview below shows the split. If all looks right, click Finish.
   
8. You may be left with some cleanup to do. As you can see, the month column is still formatted as a full date. Click the Home ribbon and change the data type to General or Text.
   

Keep Learning the Powerful Features of Excel

I’ve been using Excel for going on 30 years, and it seems I always find new, powerful ways the spreadsheet software can help me stay productive. If you’re just getting started with Excel, we have a collection of tips everybody should know about, as well as more recent Excel tips you should check out.