The YEAR function is a built-in function in Excel that returns the year part of a date as a four-digit number. For example, if the date is 12-Feb-2024, the YEAR function will return 2024.
The syntax of the YEAR function is:
=YEAR(date)
where date is a valid date value or a reference to a cell that contains a date.
How to Use the YEAR Function
To use the YEAR function, follow these steps:
- Select a cell where you want to enter the formula.
- Type
=YEAR(and then select a cell that contains a date or type a date value in double quotes, such as"12-Feb-2024". - Close the parenthesis and press Enter.
- The cell will display the year part of the date as a four-digit number.
Example: Calculating Age Using the YEAR Function
One of the common uses of the YEAR function is to calculate the age of a person based on their date of birth. To do this, we can use the following formula:
=YEAR(TODAY())-YEAR(date_of_birth)
where date_of_birth is a cell that contains the date of birth of the person.
This formula works by subtracting the year of birth from the current year, which is obtained by using the TODAY function. The TODAY function returns the current date as per the system date.
Let’s see an example of how to use this formula in an Excel table. Suppose we have the following table that contains the names and dates of birth of some people:
| Name | Date of Birth |
|---|---|
| Alice | 15-Jan-1990 |
| Bob | 23-Mar-1985 |
| Charlie | 07-Jun-1995 |
| David | 18-Oct-2000 |
| Eve | 29-Feb-1996 |
To calculate their ages, we can use the following steps:
- Select a cell next to the Date of Birth column and type
Ageas the column header. - Select the cell below the Age header and type
=YEAR(TODAY())-YEAR(and then select the cell that contains the date of birth of Alice, which is B2. - Close the parenthesis and press Enter. The cell will display the age of Alice, which is 34.
- Copy the formula to the rest of the cells in the Age column by dragging the fill handle or using the shortcut Ctrl+D. The table will look like this:
| Name | Date of Birth | Age |
|---|---|---|
| Alice | 15-Jan-1990 | 34 |
| Bob | 23-Mar-1985 | 39 |
| Charlie | 07-Jun-1995 | 28 |
| David | 18-Oct-2000 | 23 |
| Eve | 29-Feb-1996 | 28 |
We can see that the formula has calculated the ages of the people based on their dates of birth and the current date.
Other Approaches to Use the YEAR Function
Besides using the YEAR function to extract the year part of a date, there are other ways to achieve the same result. Here are some of them:
- Using the TEXT function: The TEXT function can convert a date value to a text string in a specified format. For example, we can use the formula
=TEXT(date,"yyyy")to get the year part of a date as a text string. Note that the result will be a text value, not a number, so it cannot be used for calculations. - Using the DATEVALUE and RIGHT functions: The DATEVALUE function can convert a text string that represents a date to a date value. The RIGHT function can extract a specified number of characters from the right end of a text string. For example, we can use the formula
=DATEVALUE(RIGHT(date,4))to get the year part of a date as a date value. Note that the date argument must be a text string in a format that Excel can recognize, such as"12-Feb-2024". - Using the INT and MOD functions: The INT function can round a number down to the nearest integer. The MOD function can return the remainder after dividing a number by another number. For example, we can use the formula
=INT(date/365.25)to get the year part of a date as a number. Note that this formula assumes that a year has 365.25 days, which is an approximation that may not be accurate for leap years. Alternatively, we can use the formula=MOD(date,10000)to get the year part of a date as a number. Note that this formula assumes that the date is a number in the format YYYYMMDD, such as 20240212.
