📎 This article includes 1 downloadable practice file ↓
Excel’s calendar runs January to December; Indian accounts run April to March. Every MIS report, GST reconciliation and budget sheet needs to know which financial year and quarter a date belongs to — and the formulas are short once you see the trick: shift the date back three months.
In this article
The FY start year
=YEAR(A2) - (MONTH(A2) < 4)For 15-Feb-2027 MONTH is 2, which is less than 4, so TRUE counts as 1: 2027 − 1 = 2026. For 10-Jul-2026 the result is 2026. So both dates belong to the financial year starting April 2026.
An alternative that reads more naturally: =YEAR(EDATE(A2, -3)) — move the date back three months, then take the calendar year.
Label formats
="FY " & YEAR(EDATE(A2,-3)) & "-" & RIGHT(YEAR(EDATE(A2,-3)) + 1, 2) ' FY 2026-27
="FY" & RIGHT(YEAR(EDATE(A2,-3)) + 1, 2) ' FY27Companies use different conventions — FY27, FY 2026-27, 2026-27. Pick one and use it everywhere so pivot tables group correctly.
Fiscal quarter Q1–Q4
="Q" & ROUNDUP(MONTH(EDATE(A2, -3)) / 3, 0)April–June becomes Q1, July–September Q2, October–December Q3, January–March Q4. A label like “Q3 FY27”: ="Q"&ROUNDUP(MONTH(EDATE(A2,-3))/3,0)&" FY"&RIGHT(YEAR(EDATE(A2,-3))+1,2).
Fiscal month number (April = 1)
=MOD(MONTH(A2) - 4, 12) + 1Useful for sorting months in fiscal order: April 1 … March 12.
FY start and end dates
=DATE(YEAR(A2) - (MONTH(A2) < 4), 4, 1) ' 1-Apr of the FY
=DATE(YEAR(A2) - (MONTH(A2) < 4) + 1, 3, 31) ' 31-Mar of the FYCombine with SUMIFS for year-to-date: =SUMIFS(I:I, A:A, ">="&DATE(YEAR(TODAY())-(MONTH(TODAY())<4),4,1), A:A, "<="&TODAY()).
Where people go wrong
| Mistake | Result |
|---|---|
Using YEAR(A2) as the FY |
January–March is put in the wrong year |
| Dates stored as text (“31/03/2027”) | #VALUE! — convert with DATEVALUE or Text to Columns first |
| Quarter from the calendar month | April shows as Q2 — always shift with EDATE(…,-3) |
| Labels like “FY 2026-2027” and “FY 2026-27” mixed | Pivots show the same year twice |
Practice
The combo practice workbook below has a Sales sheet of 200 orders and a task for every formula on this page. Type your formula in the yellow column; the check turns green when the answer matches. The Answers sheet has working versions.
More combinations: all formula combos · functions used here are explained in the Excel function course.
📎 Practice files for this article
- 📗Formula combinations practice workbook50 tasks: lookups, running totals, ranks, top-N, unique counts, FY, age, text extraction, list comparison, weighted averages, ageing, rolling totals, duplicates and working days — with automatic checks.⬇ XLSX · 39 KB
Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.
Stuck on a step? Ask a question and the AI answers using this article.