Skip to content

Excel function · Date & time

Excel YEAR function

YEAR returns the year of a date as a four-digit number such as 2026. Use it to group sales by year, total one year or build a year condition.

In Turkish Excel: YIL

What it does

=YEAR(A2) returns the year of the date in A2; the result is a number, not a date. That lets you add a Year column next to a date column and total by year with SUMIF or a PivotTable.

Applied to a range inside SUMPRODUCT, it needs no helper column: =SUMPRODUCT((YEAR(A2:A9)=2026)*B2:B9) adds only the rows dated 2026. Add MONTH in the same way to total one month.

Taking the difference of years with =YEAR(B2)-YEAR(A2) only subtracts calendar years; even 31 December and the following 1 January come out one year apart. For completed years use DATEDIF; for years with decimals use YEARFRAC.

Syntax

English Excel=YEAR(serial_number)
Turkish Excel=YIL(tarih)

Arguments in square brackets are optional. English Excel separates arguments with commas (,) and uses a dot for decimals; Turkish Excel uses semicolons (;) and a decimal comma. Excel shows function names in its own language when it opens the file.

Arguments

ArgumentRequiredDescription
serial_numberRequiredThe date, or the cell holding the date, whose year you want, e.g. A2. Dates typed as text are read according to the regional settings.

Examples

1Add a year column to invoices

The formula in C2 is filled down and writes the year of each invoice as a number. The column becomes a criterion for totalling or filtering by year.

C2fx=YEAR(A2)
Add a year column to invoices: example table
ABC
1Invoice DateAmountYear
218-Nov-20254,2002025
329-Dec-20253,1502025
46-Jan-20265,8002026
514-Mar-20262,9502026
621-Oct-20266,4002026
  • C2 (filled down to C6)=YEAR(A2)

C2 shows 2025 and C6 shows 2026. For the 2026 total, this column can serve as the criterion: =SUMIF(C2:C6,2026,B2:B6).

We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.

2A yearly total without a helper column

YEAR is applied to the whole date range and compares each row's year with the one in column D; SUMPRODUCT adds the amounts of the matching rows. The formula in E2 is filled down.

E2fx=SUMPRODUCT((YEAR($A$2:$A$6)=D2)*$B$2:$B$6)
A yearly total without a helper column: example table
ABCDE
1Invoice DateAmountYearTotal
218-Nov-20254,20020257,350
329-Dec-20253,150202615,150
46-Jan-20265,800
514-Mar-20262,950
621-Oct-20266,400
  • E2 (filled down to E3)=SUMPRODUCT((YEAR($A$2:$A$6)=D2)*$B$2:$B$6)

2025 gives 7,350 and 2026 gives 15,150. Type a new year in column D and copy the formula one row down.

We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.

Common errors and fixes

#VALUE!

Cause: The date in the cell is stored as text that Excel cannot read as a date, for example written in another regional format.

Fix: Convert the values to real dates; to build them from parts, use DATE.

Result is 1900

Cause: The year of an empty cell was requested; an empty cell counts as 0, which gives 1900.

Fix: Skip empty rows: =IF(A2="","",YEAR(A2)).

Year shown as a date

Cause: The result appears as a date such as 18/07/1905; the number 2026 is being shown in a date format.

Fix: Set the cell format to General or Number.

Good to know

  • To group by year and month together, =YEAR(A2)*100+MONTH(A2) produces a sortable period code such as 202610.
  • A year condition can also be built with one function: =SUMIFS(B2:B9,A2:A9,">="&DATE(2026,1,1),A2:A9,"<"&DATE(2027,1,1)).

In Nexell

Get Nexell to do it from a sentence

Type this in Nexell

Add a Year column to the sales list from the date column and give the total for each year in a separate summary

Convert to table

Bring your sales list into the tool and type this. Nexell builds the Year column and the yearly summary with real Excel formulas and shows them in the preview; in the downloaded file, the summary updates when an amount changes.

Nexell uses AI to understand the request and builds the result with real Excel formulas; the formula may differ from the example on this page. You see a preview first and nothing changes until you press Apply; formulas stay live in the downloaded .xlsx file.

Free account · no card needed · 30 credits a month

Frequently asked questions

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

Use =YEAR(A2); the result is a number such as 2026. If you want the year as text, =TEXT(A2,"yyyy") also works, but for calculations and grouping the numeric result of YEAR is better.

Related functions

All date & time functions →

Stop memorising formulas. Describe them.

Bring your table into the tool and say what you need; Nexell builds the formula and shows it in a preview.

Try it free