This article explains the syntax and the use of Excel YEAR function in Excel.
The Excel YEAR function returns a 4-digit integer representing the year corresponding to a specified date. A 4-digit year of the date is returned as an integer in the range 1900-9999.
You can use YEAR function to extracts year value from a date. Same as like DAY & MONTH, YEAR function is too rarely used on its own. Mostly, the function used in conjunction with other Excel functions and formulas, like the DATE.
For example, the formula =YEAR (DATE (2018, 4, 30) ) returns the year 2018.
- serial_number: The date of the year number you want to find. It can be a reference to a cell, a date returned by using the DATE function, result of other Excel formulas/functions or supplied straight within double quotes in the same format as system displays.
Tip: Instead of supplying date directly in YEAR function, it is more convenient to refer to a cell with date or date returned by some other formula.
Examples: Excel YEAR Function
In the following examples, YEAR function extracts 4-digit year value from dates stored in the different type of formats.
The function in cell A2, =YEAR (A2) returns the year ‘2018’ of the date (6-Feb-18).
In the example above, despite having dates in the format, YEAR function is reading the serial number of dates; hence returned year value in column B corresponding to dates given in column A.
- The serial number in cell A5 (43167) is the serial number of the date March 8, 2018, hence YEAR returns 2018 as the year value.
- The YEAR function returns ‘1900’ in cell B6 because it refers to cell A6, which is empty. And in case of an empty cell is supplied as part of a serial number, it treats empty cell as ‘0/1/1900’.
- On row 8, the cell that is referred to YEAR function is non-numeric; hence, function returned #VALUE! error.
How to find year is a leap or not?
Suppose you want to find whether the given year is a leap year or not. Assuming you have a date in cell A2, the formula is:
=IF(MONTH(DATE(YEAR(A2),2,29)) = 2,”Yes”,”No”)
Let’s break down the above formula to understand how it is working.
- YEAR function extracts the year from A2 as 2018.
- DATE function refers to year value from YEAR function and returns a date as ‘2018, 3, 1’.
- In the third step, MONTH function refers to the date returned by DATE function and checks whether that is equal to 2 or not.
- After third steps, the result comes in the form of Boolean value as TRUE/FALSE. For row 2, it returns to FALSE.
- IF function helps here in customizing the result returned by the previous step.
Note: February 2018 had only 28 days, which forced DATE function to change the month value from 2 to 3 in step 2.
To learn more about Excel YEAR function and examples, visit the Microsoft Official Website.
Additional Notes: –
- If invalid date supplied as serial_number, the function returns #VALUE! error.
- Excel handles date from January 1st, 1900, to December 31st, 9999.
- The YEAR function returns ‘0’ if the referred cell or a result of the formula returns to 0 or empty cell.
- YEAR function throws #NUM! when negative value supplied to the function.
- Use DATEVALUE function to avoid supplying date directly as text to the argument. The method is not recommended because ‘Microsoft does not guarantee correct results when a date is supplied directly as text value’.