How to extract year from a date in Excel

Excel is renowned for performing various types of calculations on numeric datasets. However, there are some cases in which we have to deal with a dataset containing dates and time. Excel has a good variety of functions and tools to handle time and date data types as well.

In today’s tutorial we’ll learn how to extract the year from a date in Excel. Let’s take a look at the dataset available above. We have the dates of birth of employees and we wish to determine the year of their births and then find the current age as well.

Excel provides us with the following methods to extract the year from any date.

• Year function
• Text function

We can also use the custom formatting to change how the date looks. However, it will not actually extract the year from the date, it will just change the format. So, this method won’t be discussed here.

Method 1: Use YEAR function to extract year from date

The YEAR function is a very useful and easy method to extract the year from date. Let’s understand the syntax of the function first.

Syntax of YEAR function

The syntax of YEAR function is very simple as given below;

YEAR(serial_number)

serial_number    

This is the date from which you want to find the year. Dates should be entered by using the DATE function, or as results of other formulas or functions. For example, use DATE(2008,5,23) for the 23rd day of May, 2008. Problems can occur if dates are entered as text. As we have the proper dates i.e., the dates of births of employees, in column B. So, we’ll use YEAR(cell reference) to extract the year from the dates. 

Step 1 – Create the formula to extract year using YEAR function

• We’ll use the following simple formula to extract the year form the date of births of employees,

=YEAR(B2)

• This will extract the required year from the date and then we’ll double click on the fill handle to copy down the formula to whole data range as shown above.

Step 2 – Extract the current year from today’s date

• To calculate the current age of any employee, we need to know the current year first and then subtract the year of birth from it. So, first find the current year by using this formula.

=YEAR(TODAY())

This will give us the present year as TODAY gives us the date of today and YEAR extracts the year from it as shown above.

Step 3 – Calculate the current age 

• Now we’ll subtract the current year from the year of birth. So, let’s use the following formula for this purpose,

=YEAR(TODAY()) – C2

C2 contains the year of birth extracted from the date of birth.

• Double click on the fill handle to implement the formula to the whole data range as shown above.

Method 2: Use TEXT function to extract year from date

The TEXT function lets you change the format of the input . Let’s understand the syntax of the function first.

Syntax of TEXT function

The syntax of the TEXT function is as follows;

TEXT(value, format_text)

value

A numeric value that you want to be converted into text.

format_text

A text string that defines the formatting that you want to be applied to the supplied value. Various formats can be applied as per requirements. The detailed discussion on the format codes of the TEXT function is beyond the scope of this article. So, we’ll stick to the one we require which is “yyyy”. This will extract the year from the complete date. 

Step 1 – Create the formula to extract year using TEXT function

• We’ll use the following simple formula to extract the year form the date of births of employees,

=TEXT(B2, “yyyy”)

• This will extract the required year from the date and then we’ll double click on the fill handle to copy down the formula to the whole data range as shown above.

We used the same dataset that we used in the first method therefore, the Current Age formula already exists in column D. When we used the TEXT function to extract the year the current age was calculated automatically. If you want to do it again you may follow steps 2 and 3 from method 1.