Table Of Contents

YEAR Function in Excel

Last Updated :

Blog Author :

Edited by :

Reviewed by :

Parameters and Arguments:

date_value/serial_value – This is the value you have entered. 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 equals the serial number 32526.
You should note that Excel can only handle dates after 1/1/1900, not before that.
Suppose 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.

YEAR Function in Excel Video

How to Open the YEAR Function in Excel?

Below are the steps to open the YEAR function in Excel:

First, you can enter the desired YEAR in the required cell to attain a return value on the argument.
You can manually open the YEAR formula dialogue box in the spreadsheet and enter the required values.
Consider the spreadsheet given below. You can see the YEAR option in Excel under the “DATE & TIME” function tab of the "Formulas" section.
Click the YEAR option. The YEAR dialog box will open where you can enter the values to obtain a return value. See the screenshot below.

How to Use YEAR Function in Excel with Examples

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

YEAR in Excel Worksheet

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

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

Example #1

Example #2

Example #3

Example #4

Example #5

Example #6

As mentioned above, the YEAR in Excel can be combined with any other formula in Excel to obtain a return value, such as the DATE function. Example no. 6 shows the application of the 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.

For example, look at the YEAR below in Excel to see the function's use as VBA code.

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

Applications

We can use the Microsoft Excel YEAR function 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: the yield 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 cell format as "General."

Things to Remember

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