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

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 functionDATE Excel FunctionThe date function in excel is a date and time function representing the number provided as arguments in a date and time code. The result displayed is in date format, but the arguments are supplied as more together in an excel spreadsheet.

YEAR in Excel VBA

The YEAR Formula can also be used as a VBA code in excelCode In ExcelIn Excel, the Code Function is used to determine the code of a character in a string. It only finds the code for the first character, thus if we use this formula as =Code(“Anand”) and =Code(“An”), the result will be 65, because the code for character A is more.

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 –

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 *