Dates and reporting
YEAR, MONTH, and DAY: extract date parts and assign fiscal years
Pull integer year, month (1–12), and day (1–31) numbers out of stored serial dates for grouping, and map April-start fiscal years with a one-line IF.
Extract integer components from a stored date
Cell A2 holds 15 March 2026 (serial 46096) and A3 holds 10 April 2026 (serial 46122). YEAR, MONTH, and DAY return plain integers rather than formatted date serials, making them safe for pivot helper columns.
| Date | YEAR | MONTH | DAY | Fiscal year (Apr–Mar) |
|---|---|---|---|---|
| 2026-03-15 (46096) | 2026 | 3 | 15 | 2026 |
| 2026-04-10 (46122) | 2026 | 4 | 10 | 2027 |
Compute an April-to-March fiscal year
For organizations whose fiscal year 2027 begins on 1 April 2026, any date with MONTH >= 4 belongs to YEAR + 1, while January through March stays in YEAR: =IF(MONTH(A3)>=4, YEAR(A3)+1, YEAR(A3)) returns 2027.
=IF(MONTH(A2)>=4,YEAR(A2)+1,YEAR(A2))Guard blank cells against the 1900 epoch trap
An empty cell evaluates as serial 0 (January 0, 1900 in Excel’s 1900 date system), causing unguarded YEAR(blank) to return 1900 and MONTH(blank) to return 1. Test A2="" first when transaction rows can be empty.
=IF(A4="","",YEAR(A4))Try the example
Use the original values or change them to test your understanding.