The YEAR function in Microsoft Excel is a built-in function that allows you to extract the year from a given date. It is a useful function when you have a list of dates and you want to extract the year from each of them.
To use the YEAR function, you need to enter it into a cell in your worksheet and provide a date as the argument. For example, if you have a date in cell A1 and you want to extract the year from it, you would enter the following formula into another cell:
=YEAR(A1)
This will return the year from the date in cell A1.
You can also use the YEAR function with a cell reference that contains a date as the argument. For example, if you have a list of dates in column A and you want to extract the year from each of them, you can enter the following formula into the first cell of column B:
=YEAR(A1)
Then, you can drag the formula down to the rest of the cells in column B to extract the year from each date in column A.
There are a few other things to keep in mind when using the YEAR function:
- If the date argument is a text value, the YEAR function will try to convert it to a date. If it is unable to do so, it will return the #VALUE! error.
- If the date argument is a blank cell or a cell that contains text that cannot be converted to a date, the YEAR function will return the #VALUE! error.
- The YEAR function only extracts the year from a date, not the month or day. If you want to extract the month or day, you can use the MONTH or DAY functions, respectively.
- The YEAR function works with dates in the 1900 date system, which means that the first day of the year is January 1, 1900.
Here are a few examples of how you can use the YEAR function in your Excel worksheets:
Example 1: Extract the year from a date
Suppose you have a list of dates in column A and you want to extract the year from each of them. You can use the YEAR function to do this. In the first cell of column B, enter the following formula:
=YEAR(A1)
Then, drag the formula down to the rest of the cells in column B. This will extract the year from each date in column A and display it in column B.
Example 2: Extract the year from a date and display it as text
Suppose you want to extract the year from a date and display it as text, rather than a number. To do this, you can use the TEXT function along with the YEAR function.
For example, if you have a date in cell A1 and you want to extract the year from it and display it as text, you can enter the following formula into another cell:
=TEXT(YEAR(A1), “0000”)
This will extract the year from the date in cell A1 and display it as text with leading zeros. For example, if the date in cell A1 is 1/1/2022, the formula will return “2022”.
Example 3: Extract the year from a date and use it in a calculation
Suppose you have a list of dates in column A and you want to calculate the number of years between each date and today’s date. You can use the YEAR function to extract the year from each date and then use it in a calculation.