Excel YEAR Function

YEAR returns the four-digit year from a date, as a plain number you can use in calculations. It doesn't care about the day, the month, or the time attached to that date, only the year. The most common mistake: feeding it a date that's actually stored as text, which returns a #VALUE! error instead of the year you expect.

Syntax

=YEAR(serial_number)

Parameter	Required	Description
serial_number	Yes	The date you want the year from. Can be a cell reference, a date typed directly (in quotes), the result of another date function like TODAY(), or a raw serial number.

Basic Example

You're building a sales report and need to group hundreds of transactions by year, using the order dates already in your worksheet.

=YEAR(orders[Order Date])

// orders[Order Date]  = the date column in your orders table

Say orders[Order Date] for a given row holds 11/3/2025. YEAR returns 2025, dropping the month and day entirely. Drag this down a column of order dates and you get a clean year value next to every transaction, ready to feed into a PivotTable or a SUMIFS formula.

How YEAR Works

Extracts the year from a date serial number

Every date in Excel is really just a number counting days since January 1, 1900. YEAR reads that underlying serial number and returns the year component. =YEAR(46000) returns 2025, the same result you'd get from =YEAR("11/3/2025").

Always returns a four-digit number

YEAR never returns a two-digit abbreviation. A date in 1998 returns 1998, not 98. This matters if you're comparing years across centuries, since there's no ambiguity to resolve.

Ignores the time portion of a date

If a cell holds a date-time value like 11/3/2025 2:45 PM, YEAR still returns 2025. Time values live in the decimal portion of the serial number; YEAR only reads the integer part.

Works directly on cell references, formulas, or serial numbers

You can nest YEAR around almost anything that resolves to a date: =YEAR(TODAY()), =YEAR(DATE(2026,7,18)), or =YEAR(A2). It doesn't require a helper cell first.

Common Use Cases

Group sales data by year

Pull the year out of each order date so you can summarize revenue per year without touching the original date column.

=YEAR(orders[Order Date])   // returns 2025 for an 11/3/2025 order

Calculate someone's age from a birth date

Subtract the birth year from the current year. This is a rough age calculation, not exact to the day, but it's fast for quick reports.

=YEAR(TODAY()) - YEAR(employees[Birth Date])

// TODAY()                     = today's date, refreshes automatically
// employees[Birth Date]       = each employee's date of birth

For an exact age that accounts for whether the birthday has passed this year, use DATEDIF instead.

Build a non-standard fiscal year

Many companies run fiscal years that don't start in January. Combine YEAR with MONTH to shift the calendar.

=YEAR(orders[Order Date]) + (MONTH(orders[Order Date]) >= 7)

// adds 1 to the calendar year for any date from July onward
// use this when your fiscal year starts July 1

If an order date is 8/14/2025, MONTH returns 8, which is >= 7, so the formula adds 1 to 2025 and returns fiscal year 2026. Adjust the 7 to match whatever month your fiscal year actually starts.

Generate a sequence of years with dynamic arrays

Build a column of consecutive years without typing each one manually, useful for year-over-year comparison tables.

=YEAR(EDATE(DATE(2020,1,1),SEQUENCE(10,1,0,12)))

// DATE(2020,1,1)    = starting date
// SEQUENCE(10,1,0,12) = 10 steps, starting at 0, incrementing by 12 months

This spills 10 rows starting at 2020 and ending at 2029. Wrapping EDATE and SEQUENCE in YEAR gives you a clean list of year numbers instead of full dates, which is easier to use as PivotTable row labels or chart axis values.

Handling Errors

YEAR throws #VALUE! when it can't interpret the input as a date or serial number. This is the only error you'll commonly see from this function.

Common causes of #VALUE!:

• The cell holds a date typed or imported as text, not a true date value
• A blank cell is being read as an empty string instead of a date
• The date was entered in a format your regional settings don't recognize, like "31/02/2026" being read as day-first when Excel expects month-first
• A formula upstream returned an error, and YEAR is trying to process that error as its input

Don't mask this error with a fallback string. A #VALUE! result almost always means the date is stored as text, and hiding it behind a generic message like "Check date format" just postpones the fix. Test for the problem directly and convert the text before it ever reaches YEAR:

=IF(ISNUMBER(orders[Order Date]), YEAR(orders[Order Date]), YEAR(DATEVALUE(orders[Order Date])))

ISNUMBER checks whether the date is already a true serial number. If it isn't, DATEVALUE converts the text first, so YEAR always receives a real date instead of failing on one stored as text.

Notes & Gotchas

What does the YEAR function do in Excel?

YEAR extracts the four-digit year from a date and returns it as a standalone number. It ignores the month, day, and time, giving you just the year component to use in calculations, comparisons, or grouping.

How do you use the YEAR function in Excel?

Type =YEAR( followed by a cell reference, a date, or another date function, then close the parenthesis. For example, =YEAR(A2) returns the year from whatever date is in cell A2. It's a single-argument function, so there's nothing else to configure.

How do I extract the year from a date in Excel?

Wrap the date in YEAR(). If the date sits in cell B5, the formula =YEAR(B5) returns just the year, such as 2026. This works whether B5 holds a typed date, a formula result, or a serial number.

How do you get the current year in Excel?

Combine YEAR with TODAY(): =YEAR(TODAY()). This returns the current year and updates automatically every time the workbook recalculates, so you never have to hardcode a year that goes stale.

What is the difference between YEAR and YEARFRAC in Excel?

YEAR returns a single whole number, the year of one date. YEARFRAC returns a decimal representing the fraction of a year between two dates, which is useful for calculating partial-year interest, prorated amounts, or age with decimal precision. Use YEAR when you need a year label; use YEARFRAC when you need elapsed time expressed as a fraction of a year.

Why does YEAR return #VALUE! with a date that looks correct?

The date is almost certainly stored as text, not as a real Excel date. This happens often with dates imported from CSV files or copied from web pages, where the date looks right but is left-aligned in the cell instead of right-aligned. Fix it by running the cell through DATEVALUE first, or use Text to Columns to convert the whole range at once.

Does YEAR work with dates before 1900?

Mostly, in a way that surprises people. Excel's date system nominally starts at January 1, 1900 (serial number 1), but small negative serial numbers don't throw an error, they just keep counting backward: YEAR(-5) returns 1899, not #VALUE!. Deeply invalid or out-of-range serial numbers can eventually produce an error, but the boundary isn't as hard, or as close to 1900, as it might seem. Don't rely on YEAR to flag an out-of-range date for you; validate the input directly with a comparison or ISNUMBER check if that matters to your formula.

What happens if a date is entered in the wrong regional format?

Excel reads dates according to your system's locale settings, so a date typed as 3/11/2025 means March 11 in the US but November 3 in the UK. If your workbook moves between regions or the data was imported from a different locale, YEAR can silently pull the wrong year with no error at all, because both interpretations are valid dates. Standardize on DATE(year, month, day) in formulas instead of typed date strings to avoid this entirely.

Related Functions

Function	Use this when...
DATE	You want to build a date from separate year, month, and day values instead of extracting one.
DATEDIF	You need an exact age or duration between two dates, not just a year label.
TODAY	You need the current date to feed into YEAR or another date formula.