Summary

The Excel YEAR function returns the year component of a date as a 4-digit number. You can use the YEAR function to extract a year number from a date into a cell or to extract and feed a year value into another formula, like the DATE function.

Purpose 

Get the year from a date

Return value 

Year as 4-digit number

Arguments 

  • date - A valid Excel date.

Syntax 

=YEAR(date)

How to use 

The YEAR function extracts the year from a given date as a 4-digit number. For example:

=YEAR("23-Aug-2012") // returns 2012
=YEAR("11-May-2019") // returns 2019

You can use the YEAR function to extract a month number from a date into a cell, or to feed a month number into another function like the DATE function:

=DATE(YEAR(A1),1,1) // first of same year

See below for more examples of formulas that use the DATE function.

Note: dates are serial numbers in Excel, and begin on January 1, 1900. Dates before 1900 are not supported. To display date values in a human-readable date format, apply the number format of your choice.

Notes

  • Date must be a valid Excel date.
  • YEAR will return a #VALUE error on text values.

YEAR function examples

Excel formula: Date is same month and year

Date is same month and year

In the example, the formula in cell D6 contains this formula: =MONTH(B6)...
Excel formula: Zodiac sign lookup

Zodiac sign lookup

The goal of this example is to look up the correct astrological or zodiac sign for a given birthdate, using the table shown in B5:F15. These are based on the Western zodiac signs described here . Zodiac signs are used in horoscopes, which are a kind of forecast of a person's future, based on the...
Excel formula: Sequence of years

Sequence of years

The goal is to generate a series of dates one year apart. In the current version of Excel, the easiest way to do this is with the SEQUENCE function together with the DATE, YEAR, MONTH, and DAY functions. In older versions of Excel, you can use the same date functions and a more manual approach...
Excel formula: Count birthdays by year

Count birthdays by year

In this example, the goal is to count birthdays by year. The source data is an Excel Table named data in the range C5:C16. The birthdays we want to count are in the Birthday column. In column E, the years of interest have been previously entered. The easiest way to solve this problem is with the...
Excel formula: Sum by fiscal year

Sum by fiscal year

The goal of this example is to sum amounts by fiscal year, when the fiscal year begins in July. The first approach is a self-contained formula based on the SUMPRODUCT function. The second method uses SUMIF with column D as a helper column. Either approach will work correctly, and the best option...
Excel formula: Count dates in given year

Count dates in given year

The YEAR function extracts the year from a valid Excel date . For example: =YEAR("15-Jun-2021") // returns 2021 In this case, we are giving YEAR and array of dates in the named range dates : YEAR(dates) Because dates contains 11 cells, we get back 11 results in an array like this: {2018;2017;2019;...
Excel formula: Get percent of year complete

Get percent of year complete

The goal in this example is to return the amount of time completed in a year as a percentage value, based on any given date. In other words, when given the date July 1, 2021, the formula should return 50% since we are halfway* through the year. *By default, the YEARFRAC function uses a 30/360 day...
Excel formula: Add years to date

Add years to date

Working from the inside out, the YEAR, MONTH, and DAY functions extract those respective date components: =YEAR(B5) // 1960 =MONTH(B5) // 3 =DAY(B5) // 8 At the outer level, the DATE function simply reassembles the component values back into a valid Excel date. To add years to the date, we just...
Excel formula: Sum by year

Sum by year

In this example, the goal is to calculate a total for each year that appears in column F. All data is in an Excel Table called data in the range B5:D16. The main challenge is that we have dates in column B, but we don't have separate year values to work with. The simplest way to solve this problem...
Excel formula: Data validation allow weekday only

Data validation allow weekday only

Data validation rules are triggered when a user adds or changes a cell value. This custom validation formula uses the WEEKDAY function to get a numeric value, 1-7, corresponding to to a week beginning Monday (1) and ending Sunday (7). To get a number for a Monday-based week, the return_type...
Excel formula: Get fiscal year from date

Get fiscal year from date

In this example, the goal is to return the fiscal year for any given date, where a fiscal year starts in July as seen in the worksheet. By convention a fiscal year is denoted by the year in which it ends. So, if a fiscal year begins in July, then the date August 1, 2018 is in fiscal year 2019. The...
Excel formula: Get year from date

Get year from date

The YEAR function takes just one argument, the date from which you want to extract the year. In the example, the formula is: =YEAR(B4) B4 contains a date value for January 5, 2016. The YEAR function returns the number 2016 representing the year of the date. Note that you can use YEAR to extract the...
Excel formula: Year is a leap year

Year is a leap year

The core of this formula is the DATE function , which will automatically adjust to month and year values that are out of range. In the formula, year is passed into date unchanged, along with 2 for month (February) and 29 for the day. In leap years, February has 29 days, so the DATE function will...
Excel formula: Data validation date in specific year

Data validation date in specific year

Data validation rules are triggered when a user adds or changes a cell value. This custom validation formula simply checks the year of any date against a hard-coded year value using the YEAR function. When a user enters a value, the YEAR function extracts and compares the year to 2016: =YEAR(C5)=...
Excel formula: Convert date to Julian format

Convert date to Julian format

This formula builds the final result in 2 parts, joined by concatenation with the ampersand (...

YEAR function videos

How to create date and time series with formulas

Although you can use Excel's AutoFill feature to fill in a series of dates and times, you can also do the same thing with formulas. The advantage of using a formula is that you can easily change the starting value and generate a new series. Let's take a look. Often you'll need to generate a series...

How to calculate years and months between dates

In this video we'll look at how to calculate the number of years or months between dates using a function called DATEDIF and a function called YEARFRAC . The DATEDIF function is a "compatibility" function that comes from Lotus 1-2-3. For reasons that are unknown, it's only documented in Excel 2000...

How to work with dates

Excel contains special functions that will let you extract the day, month, and year from a valid date. Let's take a look. Here we have a set of random dates in column B. First, I'll add a formula to column C to pick up the date values in B and format them with the General format, so we can see the...

Related functions

Excel YEAR function

YEAR Function

The Excel YEAR function returns the year component of a date as a 4-digit number. You can use the YEAR function to extract a year number from a date into a cell or to extract and feed a year value into another formula, like the DATE function.
Excel MONTH function

MONTH Function

The Excel MONTH function extracts the month from a given date as number between 1 to 12. You can use the MONTH function to extract a month number from a date into a cell, or to feed a month number into another function like the DATE function.
Excel DAY function

DAY Function

The Excel DAY function returns the day of the month as a number between 1 to 31 from a given date. You can use the DAY function to extract a day number from a date into a cell. You can also use the DAY function to extract and feed a day value into another function, like the...
Excel DATE function

DATE Function

The Excel DATE function creates a valid date from individual year, month, and day components. The DATE function is useful for assembling dates that need to change dynamically based on other values in a worksheet.
Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.

Formulas

Videos

Functions

Links