YEAR Function in Excel

YEAR Function in Excel

Year function in excel is a date function in excel which is used to calculate the year value from a given date, this function takes a date as an argument and returns a four digit numeric value as integer value which represents the year of the given date, the method to use this formula is as follows =Year (Serial Number ), Serial number is the date argument given to the function.

YEAR Formula in Excel

YEAR Formula in Excel

Parameters and Arguments:

date_value/serial_value – This is referred to as the value which you have entered, and it should be a valid date, which is then used to return the year value.

Return Value:

The return value in the YEAR Formula will be a numeric value, which ranges between 1900 and 9999.

Usage Notes

  • The date value entered in the YEAR in excel must be a valid Excel date in serial number format. For example, if you enter the date, Jan 1, 2000, it is equal to the serial number 32526.
  • You should note that Excel can only handle dates after 1/1/1900, not before that.
  • If you put any invalid data in the YEAR formula in excel, you will most probably get an error, but no valid return value.
  • You can use the TODAY function in Excel with YEAR to get the year from the current date as a return value.

How to Open the YEAR Function in Excel?

Below are the steps to open year function in excel –

  1. You can simply enter the desired YEAR in the required cell to attain a return value on the argument.

  2. You can manually open the YEAR formula dialogue box in the spreadsheet and enter the required values.

  3. Consider the spreadsheet given below. You can see the option of YEAR in excel under the Date/Time functions tab of the Formula section.

    YEAR Function 1

  4. Click the YEAR option, and the YEAR dialogue box will open where you can enter the values to obtain a return value. See the screenshot below.

    YEAR Function 2

How to Use YEAR Function in Excel with Examples

Let’s look below at some of the examples of the YEAR function. These examples will help you in exploring the use of the YEAR formula.

You can download this YEAR Function Excel Template here – YEAR Function Excel Template

YEAR in Excel Worksheet

YEAR Function Example 1

Based on the above Excel spreadsheet, let’s consider the below examples and see the YEAR in Excel return based on the formula of the YEAR function.

YEAR Function Example 1-1

Consider the below screenshots of the above examples for clear understanding.

Example #1

YEAR Example 1

Example #2

YEAR Example 2

Example #3

YEAR Example 3

Example #4

YEAR Example 4

Example #5

YEAR Example 5

Example #6

YEAR Example 6

As has been mentioned above that, the YEAR in excel can be combined with any other formula in excel to obtain a return value, such as DATE Function. Example no. 6 shows the application of YEAR and DATE Excel function together in an excel spreadsheet.

YEAR in Excel VBA

The YEAR Formula can also be used as a VBA code in excel.

Look at the below YEAR in excel example to see the use of the function as VBA codeVBA CodeVBA code refers to a set of instructions written by the user in the Visual Basic Applications programming language on a Visual Basic Editor (VBE) to perform a specific more.

YEAR in Excel VBA

In the above example, the variable which is called LYear will now have the value of 2017.


The Microsoft Excel YEAR Function can be used for various purposes and applications within the spreadsheet. Some of the common applications of YEAR formula spreadsheets are given below –

  • Data validation date in a specific year
  • Count dates in a given year
  • Converting dates to Julian format
  • Finding out if a year is a leap year
  • Making a series of dates by year
  • Getting the nth day of the year
  • Adding years to date
  • Getting percent of year complete
  • Getting fiscal year from the date

YEAR Excel Function Common Problem

Sometimes you can face a common problem that the return value of the YEAR function is not any numerical value, but it looks like a date such as “01/01/1900”. Most likely, this problem will arise when you have your cell format set as ‘date.’ To correct this problem, you have to set the format of the cell as ‘general.’

Things to Remember

  • The YEAR formula allows you to return the year component of an entered date as a four-digit number.
  • The YEAR formula can also be used for extracting and feeding a year value into any other excel worksheet formula, such as the DATE function or TODAY function.
  • Dates that are supplied in the YEAR in excel should be supplied as serial numbers or reference to cells that have valid dates or valid date values.
  • You should note down that the value which is returned by the YEAR function will be Gregorian values. This will have no relation with the display format for the supplied date value in the YEAR Formula in Excel.
  • The YEAR can understand dates in all possible formats perfectly.

YEAR Function in Excel Video

Recommended Articles

This has been a guide to the YEAR Function in Excel. Here we discuss the YEAR Formula in excel and how to use YEAR formula along with excel example and downloadable excel templates. You may also look at these useful functions in excel –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion

Reader Interactions

Leave a Reply

Your email address will not be published. Required fields are marked *