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
=YEAR(serial_number)=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
| Argument | Required | Description |
|---|---|---|
serial_number | Required | The 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.
=YEAR(A2)| A | B | C | |
|---|---|---|---|
| 1 | Invoice Date | Amount | Year |
| 2 | 18-Nov-2025 | 4,200 | 2025 |
| 3 | 29-Dec-2025 | 3,150 | 2025 |
| 4 | 6-Jan-2026 | 5,800 | 2026 |
| 5 | 14-Mar-2026 | 2,950 | 2026 |
| 6 | 21-Oct-2026 | 6,400 | 2026 |
- 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.
=SUMPRODUCT((YEAR($A$2:$A$6)=D2)*$B$2:$B$6)| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Invoice Date | Amount | Year | Total | |
| 2 | 18-Nov-2025 | 4,200 | 2025 | 7,350 | |
| 3 | 29-Dec-2025 | 3,150 | 2026 | 15,150 | |
| 4 | 6-Jan-2026 | 5,800 | |||
| 5 | 14-Mar-2026 | 2,950 | |||
| 6 | 21-Oct-2026 | 6,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
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 tableBring 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
- MONTHAY · Month of a date (1–12).
- DAYGÜN · Day of the month (1–31).
- DATETARİH · Builds a date from year, month and day.
- DATEDIFETARİHLİ · Difference between dates ("Y", "M", "D", "YM", "MD", "YD").
- SUMPRODUCTTOPLA.ÇARPIM · Multiplies matching items of arrays and sums them; the classic conditional sum: SUMPRODUCT((City="Ankara")*Amount).