Extract the year from a date
=YEAR(B2)Excel · result on the sample sheet: 5 rows (C2:C6)
Describe what you need and get a working YEAR formula for Excel and Google Sheets, tested on real data before you copy it. YEAR returns the four-digit year from a date.
Each formula below was calculated on its own sample sheet. Open one to see the sheet, why it works, and to try it yourself.
=YEAR(B2)Excel · result on the sample sheet: 5 rows (C2:C6)
=A2-DATE(YEAR(A2),1,0)Excel · result on the sample sheet: 5 rows (B2:B6)
=DATE(YEAR(A2),12,31)-A2Excel · result on the sample sheet: 5 rows (B2:B6)
=FILTER(A2:E11,(MONTH(B2:B11)=MONTH(H1))*(YEAR(B2:B11)=YEAR(H1)))Excel · result on the sample sheet: 4 rows (G3:K6)
=TEXT(A2,"yyyy-mm-dd")Excel · result on the sample sheet: 5 rows (B2:B6)
=SUMIFS(C2:C10,A2:A10,">="&DATE(YEAR(F1),1,1),A2:A10,"<="&F1)Excel · result on the sample sheet: $15,580
=SUMIFS($C$2:$C$10,$A$2:$A$10,">="&DATE(E2,1,1),$A$2:$A$10,"<="&DATE(E2,12,31))Excel · result on the sample sheet: 3 rows (F2:F4)
=SUMPRODUCT((YEAR(EDATE($A$2:$A$9,6))=E2)*$C$2:$C$9)Excel · result on the sample sheet: 2 rows (F2:F3)
You describe the task in plain English, and it writes the formula, using YEAR where that's the right tool, for your table or a sample one. The formula is run in our engine in Excel and Google Sheets mode before you see it, so you get its result, not just a guess.
YEAR returns the four-digit year from a date.
Yes. Each example below opens a page where you try the formula in a live grid and it's checked as you go, and the YEAR function page has a practice exercise.