How to use Excel YEAR Function

0
249

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.

Uses:

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.

Syntax:

YEAR (serial_number)

Argument:

  • 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).

Excel Year Function

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”)

Example - Excel Year Function

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’.

Related Articles:-

MONTH | DAY | TODAY  | DATE | EOMONTH | EDATE

LEAVE A REPLY

Please enter your comment!
Please enter your name here