Excel YEAR Formula
The YEAR formula in Excel is used to calculate the year from a given date. It returns a year (as an integer in YYYY format) corresponding to a given date. General Syntax for the YEAR formula in excel is as follows:
The YEAR function syntax has the following argument:
- serial_number: Required, represents a date whose corresponding year is to be computed or returned.
In Excel, dates are stored as sequential serial numbers (starting from January 1, 1900), and the YEAR function operates on these numbers. January 1, 1900, is stored as serial number 1 in Excel.
When combined with other Date/Time functions, this function can be quite useful in financial modeling.
Some instances where this can be helpful are:
- When a company decides to give some bonus only to employees who joined before a specific year
- When an insurance company decides to reduce the premium of those only who have taken the policy before a specific year.
- When a retail shop decides to give some discount to those customers only who have been associated with them since a given year.
How to use Year Formula in Excel?
Following are the ways with which YEAR formula in excel can be used:
Click on the cell where we wish to return a year of a given date, then type ‘=’ followed by ‘YEAR’, and enter the argument for which YEAR is desired:
Select the function ‘YEAR’ and click on ‘OK’.
So, another dialog box would appear that would ask for arguments to be entered. Enter the arguments and then click on ‘OK’.
If we wish to find the year corresponding to a given date, then the YEAR formula would work as follows:
We can see that the argument passed to the YEAR formula is a reference to a cell (A2) containing a date. So, with this argument passed to the function, the function returns the year corresponding to that date in cell B2.
So, we see that if 24/03/2019 is passed as date, then 2019 is returned.
Now, let’s say in the above example we supply the date argument as a DATE function to the YEAR function, and then see how the YEAR formula in excel would work:
We can see that the argument passed to the YEAR function is a date that is provided via DATE function. So, with this argument passed to the function, the function returns the year corresponding to that date in cell A2.
So, we see that if we pass the argument as DATE (2019, 04, 08), then 2019 is returned.
Now, let’s say the argument or parameter passed to the YEAR function is a function returning present date, i.e via TODAY() function. In this case, the excel YEAR formula would work as follows:
We can see that the argument passed to the YEAR function is a function: TODAY (), returning the current date. So, when this is passed as an argument to the function, the function returns the year corresponding to the current date in cell A2.
Now, if we wish to compute the elapsed years (i.e number of years that have elapsed from a given date to present), then the excel YEAR formula would work as follows:
=YEAR(TODAY()) – YEAR(A2)
We can see that to calculate the number of years that have elapsed from current date to a given date can be calculated by using YEAR formula in excel as above, i.e subtracting the year values of the current date and given the date, and getting an integer value by changing its format type to ‘General’.
So as illustrated in the screenshot, we can see that the number of years from 2016 to 2019 (current year using TODAY () ) is 3.
Things to Remember about YEAR Function in Excel
- Excel YEAR formula is a built-in function in Excel and is categorized as a Date/Time function.
- Excel YEAR formula can also be used as a worksheet function.
- The date provided as argument or parameter to the YEAR function should be cell references containing dates, or serial numbers, or dates returned from other formulas. This is so because, with dates entered as text, the YEAR function might not give the correct result always as different systems have different settings of date interpretations.
- If the argument passed to the YEAR function is some text value, then the YEAR function would return #VALUE! Error.
Following screenshot illustrates this:
- The date provided as argument or parameter to the YEAR function must be a valid Excel date.
- Irrespective of the date supplied as an argument and the display format, the YEAR function returns values as per the Gregorian calendar.
- A date can also be displayed as a year, i.e irrespective of how the date is stored in a cell, Excel can display only its year without changing the cell value or date. So, we can have a full date stored in the cell, but getting only the year displayed.
We need not use the excel YEAR formula for this. This can be achieved just by changing the format type as follows:
- Click on the ‘Home’ tab and expand the ‘Number Format’ dropdown and click on ‘More Number Formats’ :
- Select ‘Custom’ and in the ‘Type’ box, type: ‘yyyy’ and click ‘OK’.
So, we see that the cell value changes from full date to only the year being displayed.
Also, this format would be saved in the ‘Type’ list the next time we need it. If we wish to display the year as a two-digit year, then we can type: ‘yy’ instead of ‘yyyy’ in the Type list.
- In order to get an integer value returned with the YEAR function, the resultant cell has to be formatted as ‘General’ rather than as a ‘Date’.
- YEAR function can understand date in all the possible formats. So, the date passed as argument or parameter to the YEAR function can be in any possible accepted format.
This has been a guide to YEAR Formula in Excel. Here we will learn how to use the YEAR formula in Excel along with practical examples and a downloadable excel template. You may learn more about excel from the following articles –