Calderbank Veterinary Group had a 2.5% rebate waiting on £900,000 of spend. The sheet said £874,300.00 and the claim was never filed. The spend was £1,046,900.00 — in the twelve months the contract actually measured.
Calderbank runs eleven practices and buys drugs and consumables from one distributor, Pellmore. The contract pays 2.5% of qualifying spend once spend passes £900,000.00 in Pellmore's rebate year, which runs 1 April to 31 March — the same year end as Calderbank's own accounts.
The claim is prepared each June by the finance assistant. The working was one formula over the purchase ledger:
=SUMIFS(Ledger[Net],Ledger[Year],2025) → 874,300.00
£25,700.00 short. The file was closed with "threshold not met" typed in a cell beside it, and nobody looked again.
Ledger[Year] was =YEAR([@Date]). It is the most natural helper column anybody ever writes, and it answers a question no agreement in that building had asked, because nothing Calderbank signed ran January to December.
| Window | What it covers | Spend | Against £900,000.00 |
|---|---|---|---|
| Calendar 2025 | Jan–Dec 2025 | £874,300.00 | £25,700.00 short |
| Calendar 2026, as far as it went | Jan–Mar 2026 | £332,350.00 | £567,650.00 short |
| FY26 | Apr 2025 – Mar 2026 | £1,046,900.00 | £146,900.00 over |
The two windows differ by one quarter at each end, and both quarters were unusual. January to March 2025 was £159,750.00, before two practices joined the group. January to March 2026 was £332,350.00, with their first full stock orders in it.
So a calendar year dropped the strong quarter and kept the weak one. The rebate was £26,172.50. The claim window closed on 30 June 2026 and the error surfaced on 14 September, while somebody was building the FY27 forecast.
The money was gone, and it was not the expensive part. The FY27 terms were negotiated off the same calendar figure, so Calderbank argued for the threshold to be cut to £850,000.00 — a concession it did not need — instead of asking for a better rate on a million pounds of spend.
01One Year of Spend, Added Up Four Ways
One Year of Spend, Added Up Four Ways, and Only Two of Them Were the Contract's Year
The Ledger table holds one purchase-ledger line per delivery from Pellmore: date, practice, product group, net and VAT. The contract pays 2.5% of net spend once net spend passes £900,000.00 inside the rebate year, 1 April to 31 March, and the claim has to be filed by 30 June. Every figure in the Spend column comes from the same table and the same rows; the only thing that changes down the column is which twelve months the formula asks for, and whether the last day of those twelve months is included with `<=` or excluded by `<` the day after. Two of the windows clear the threshold and two do not, two of the quarters carry the same label and three months of difference, and the table Calderbank actually used is the first row.
fxCells with formulas are highlighted in green
Hover over formula cells to see the formula and highlight referenced cells
Rows three and four are the same twelve months reached by two routes. Rows one and two are the same ledger under a label the contract never used. Nothing in the data changes between them. Only the window does.
Scenario: Open the sheet that proves a threshold, a target or a covenant. Find the column that decides which year a row belongs to. If it reads =YEAR([@Date]), put =YEAR(EOMONTH([@Date],9)) beside it and compare. Every row where the two disagree is a row in the wrong year.
02What a Fiscal Year Is, in Cell Terms
A fiscal year is a twelve-month window starting on the first of some month other than January, and Excel has no setting for it. YEAR returns the calendar year, MONTH returns the calendar month, and no option anywhere changes that.
So a fiscal calendar is something you build in a column, once, next to the dates. Two facts define it: the month the year starts in, and whether the label names the year it starts in or the year it ends in.
Put the start month in a cell of its own and name it FYStart. Every formula below reads that cell, which makes a change of year end one edit rather than a hunt through the workbook for the number 4.
This article uses a year starting 1 April and labelled by the calendar year it ends in, so FY26 runs 1 April 2025 to 31 March 2026. That is the common UK convention, it is not universal, and section 10 covers the others.
Scenario: In the workbook that carries your fiscal reporting, put the start month in one cell, name it FYStart, and type the convention beside it in plain words: "FY labelled by the year it ends". Then repoint one formula at that cell instead of a typed 4.
03The Fiscal Year Label in One Formula
Shift the date forward to the calendar year you want to name it after, and read the year off that. For an April start labelled by the ending year, the shift is nine months.
=YEAR(EOMONTH(A2,9)) → 2026 for every date from 01/04/2025 to 31/03/2026
=YEAR(EOMONTH(A2,13-FYStart)) the same thing, driven by the start month
=YEAR(A2)+(MONTH(A2)>=FYStart) no EOMONTH, same answer, same labelling
EOMONTH(A2,9) returns the last day of the month nine months on, and YEAR of that is the year the month falls in. The day of the month never matters, which is why EOMONTH is safe where adding 275 days is not.
To label by the starting year instead — the Australian convention, among others — shift backwards: =YEAR(EOMONTH(A2,-(FYStart-1))). For an April start that is -3, so April 2025 to March 2026 comes out as 2025.
Scenario: Add an FY column holding =YEAR(EOMONTH([@Date],13-FYStart)) and a Cal Year column holding =YEAR([@Date]). Put =SUMPRODUCT(--(Ledger[FY]<>Ledger[Cal Year])) underneath. Over a full year of rows it should come back at roughly a quarter of them.
04Fiscal Quarters Without a Nested IF
The calendar quarter is =ROUNDUP(MONTH(A2)/3,0), and in a year that starts in April it is wrong by exactly one quarter on every row of the table. April is calendar Q2 and fiscal Q1, all year, without exception.
=INT(MOD(MONTH(A2)-FYStart,12)/3)+1 → 1 for Apr-Jun, 2 for Jul-Sep, 3 for Oct-Dec, 4 for Jan-Mar
=MOD(MONTH(A2)-FYStart,12)+1 → the fiscal period number, 1 to 12
MOD is what makes it general. Subtracting the start month gives a number from -11 to 11, and MOD(…,12) folds the negatives back into 0 to 11 — so the months before the year starts need no special case.
A nested IF, a CHOOSE or a SWITCH all reach the same answer and all write the start month into a dozen places. That is a dozen edits the day a group moves its year end, and a dozen chances to miss one.
Scenario: Put =ROUNDUP(MONTH([@Date])/3,0) and =INT(MOD(MONTH([@Date])-FYStart,12)/3)+1 side by side over a full year of rows. Every row will differ. Then check which of the two your existing quarter column is.
05Period Start Dates Sort, Labels Do Not
A period label is text, and text sorts alphabetically. "Q10" comes before "Q2" the moment anybody numbers periods one to twelve, and a sort by label puts FY26 Q4 above FY26 Q1 as soon as the prefix changes.
Carry a real date for the period and format it for display. The first day of a row's fiscal quarter is one EOMONTH:
=EOMONTH(A2,-MOD(MONTH(A2)-FYStart,3)-1)+1 → 01/01/2026 for any date in Jan-Mar 2026
=EOMONTH(A2,-1)+1 → the first of the row's own month
Sort, chart and pivot on that column, and show the label beside it for people to read. A date sorts chronologically in every tool, survives a change of locale, and groups without anybody maintaining a custom list.
Build the label from the same date: ="FY"&RIGHT(YEAR(EOMONTH(A2,9)),2)&" Q"&(INT(MOD(MONTH(A2)-FYStart,12)/3)+1). Keep the brackets around the quarter arithmetic — without them Excel adds 1 to the joined text and returns #VALUE!.
Scenario: Add a Period Start column using the EOMONTH formula above, then sort your report by it instead of by the label. If the row order changes, the report has been in alphabetical order of period name rather than in time order.
06Boundaries: Use the Next Period's First Day
A fiscal window needs two criteria, and the second should name the next year's first day rather than this year's last:
=SUMIFS(Ledger[Net],Ledger[Date],">="&DATE(2025,4,1),Ledger[Date],"<"&DATE(2026,4,1)) → 1,046,900.00
=SUMIFS(Ledger[Net],Ledger[Date],">="&DATE(2025,4,1),Ledger[Date],"<="&DATE(2026,3,31)) → 1,041,600.00
The second version is £5,300.00 light. Four lines imported from the distributor's portal are stamped 31/03/2026 14:52, and a date carrying a time is a larger number than the date, so a <= on the last day excludes the last day's afternoon.
Strip the time once, on the way in, rather than defending every criterion downstream: =INT([@Raw Date]) in the date column, or a Date rather than DateTime type in Power Query. Then use < the next start anyway, because the next import will not ask permission.
Scenario: Run =SUMPRODUCT(--(Ledger[Date]<>INT(Ledger[Date]))) over your date column. Anything but zero means some rows carry a time of day, and every <= boundary in the workbook is dropping part of a day. On Calderbank's ledger it reads 4.
07Fiscal Year to Date, and the As-At Date
Year to date needs the fiscal year's first day, derived from the as-at date rather than typed in:
FY start: =DATE(YEAR(EOMONTH(AsAt,13-FYStart))-1,FYStart,1)
YTD: =SUMIFS(Ledger[Net],Ledger[Date],">="&FYStartDate,Ledger[Date],"<="&AsAt)
Last year: =SUMIFS(Ledger[Net],Ledger[Date],">="&EDATE(FYStartDate,-12),Ledger[Date],"<="&EDATE(AsAt,-12))
Keep the as-at date in one cell. Every year-to-date figure in the pack then moves together, and "as at" stops being something a reader has to infer from wherever the data happens to stop.
The prior-year column shifts both ends back twelve months with EDATE, which lands on the same day of the same month a year earlier and copes with February. Comparing part of one year against all of another is the other half of this mistake, and it is harder to spot than a wrong label.
Scenario: Put the as-at date in one named cell, point every YTD formula at it, and set it to a date mid-quarter. Check that the prior-year column moved too. If it did not, the pack is comparing eleven months against twelve and calling the difference a trend.
Try it in the grid08Pivots Group Calendar Quarters Only
A PivotTable groups a date field by Years, Quarters and Months, and its quarters are always January to March, April to June, and so on. There is no setting for a year that starts in April. Its Years are calendar years for the same reason.
So group on your own columns instead. Put FY, Fiscal Quarter and Period Start in the source table and drag those fields into Rows or Columns; leave the raw date ungrouped. The pivot then knows nothing about calendars, which is the point.
Grouping a date field creates hidden
Years (Date)andQuarters (Date)fields on the data source, and every pivot built from the same cache shares them. Ungrouping in one report changes the others, which is its own morning.
Scenario: Right-click the date field in your pivot, choose Group, and read what Quarters gives you. If the first quarter starts in January and your year starts in April, remove the grouping and drag Fiscal Quarter in from the table instead.
09A 4-4-5 Calendar Needs a Table
Retail and manufacturing calendars divide the year into weeks rather than months: four weeks, four weeks, five weeks, repeated. Periods start on a Sunday or a Monday and almost never on the first of a month.
No arithmetic on MONTH will find those boundaries, because they are not month boundaries. Nor will anything find the 53rd week, which appears every five or six years to stop the calendar drifting away from the solar year.
A week-based calendar is a table: one row per period, with its start date, end date, period number and fiscal year. The lookup is approximate and downwards, against the start date:
=XLOOKUP(A2,Calendar[Period Start],Calendar[Period],,-1)
=XLOOKUP(A2,Calendar[Period Start],Calendar[FY],,-1)
The -1 means "exact match, or the next smaller item", so any date inside a period finds that period's row. Keep the table sorted ascending, publish it once a year, and never let two formulas work the boundaries out independently.
Scenario: If your periods are weeks, build next year's calendar table now and check that its days add to 364 or 371. Then replace every month-based period formula with =XLOOKUP([@Date],Calendar[Period Start],Calendar[Period],,-1).
10What FY26 Means Depends Who Writes It
The label is a convention rather than a fact, and the conventions disagree with each other.
| Whose year | Starts | "FY26" usually covers |
|---|---|---|
| UK company, April year end | 1 Apr 2025 | Apr 2025 – Mar 2026 |
| US federal government | 1 Oct 2025 | Oct 2025 – Sep 2026 |
| Australian entity | 1 Jul 2025 | Jul 2025 – Jun 2026 |
| US retailer, late-January year end | 1 Feb | Feb 2025 – Jan 2026, or Feb 2026 – Jan 2027 |
Those first three are labelled by the year the window ends in. The retail row is labelled either way depending on the retailer, which is why the same four characters on a file name can mean windows a year apart.
So write it down. One cell saying "FY runs 1 April to 31 March, labelled by the year it ends" costs nothing and settles every argument a reader could otherwise have with the numbers.
And check the counterparty's year rather than assuming yours. Calderbank's rebate year happened to match its own accounts, which is luck and not a rule: a supplier's rebate year, a lease year and a tax year can each start somewhere different.
Scenario: Open the agreement behind your largest rebate, bonus or covenant and find the sentence defining its measurement period. Type those two dates into cells in the workbook that proves it, and point the SUMIFS boundaries at those cells.
11Five Checks of One Cell Each
=SUMPRODUCT(--(Ledger[FY]<>YEAR(EOMONTH(Ledger[Date],13-FYStart)))) → 0 the FY column agrees with the rule
=SUMPRODUCT(--(Ledger[Date]<>INT(Ledger[Date]))) → 0 no row carries a time of day
=SUM(Q1:Q4)-SUMIFS(Ledger[Net],Ledger[FY],2026) → 0.00 the quarters cover the year once
=COUNTIFS(Ledger[Date],">="&FYStart,Ledger[Date],"<"&EDATE(FYStart,12)) → 1,184 rows inside the window
=TEXT(FYStart,"dd mmm yyyy")&" to "&TEXT(EDATE(FYStart,12)-1,"dd mmm yyyy") → "01 Apr 2025 to 31 Mar 2026"
The last one is the cheapest and catches the most. A window written out in words, parked beside the total it produced, is something a reader can disagree with. £874,300.00 under the heading "annual spend" is not.
Check the count as well as the total. A window can hold the wrong rows and still add to a plausible number, and the row count is the only cheap way to see the window itself rather than its output.
Scenario: Add that last check to the sheet behind your most recent year-end figure, in the cell directly above or below it. Read the two dates it prints and compare them to the agreement, the board paper or the signed accounts.
Try it in the grid12Ten Traps
=YEAR([@Date])as the year column. Right in a calendar-year business, wrong in every other, and it never produces an error to warn you.=ROUNDUP(MONTH(A2)/3,0)as the quarter. Displaced by one quarter for an April year, two for a July year, three for an October year.<=the last day of the year. Drops every row stamped with a time of day.<the next year's first day does not.- Sorting on the period label. Text order, so Q10 lands before Q2 and a quarter sorts by its name rather than its place in the year.
- Grouping a pivot's date field by Quarters. Calendar quarters, always, and the grouping is shared by every pivot on that cache.
- A year to date against a full prior year. Eleven months against twelve looks exactly like a collapse in demand.
- Hard-coding the start month. A dozen
IFs have to be found again the day the year end moves, and one of them will not be. - Assuming the counterparty's year is yours. Rebate years, lease years and tax years each start where their own contract says.
- Deriving 4-4-5 boundaries by arithmetic. Week-based periods do not fall on month ends, and some years have a 53rd week.
- Leaving the convention unwritten. FY26 means a different twelve months to the group, the supplier and the auditor.
What to Take Away
Excel has no idea what your fiscal year is and never will. YEAR and MONTH answer calendar questions, and every fiscal question is a calendar question plus an offset that you have to supply.
Supply it once. One cell for the start month, one column for the fiscal year, one for the quarter, one for the period start date — and every total, pivot and chart in the workbook then reads a fiscal calendar it cannot misread.
Calderbank's loss was one helper column answering the wrong question perfectly. =YEAR([@Date]) is correct Excel, and £26,172.50 went unclaimed because the contract was measuring a different twelve months and nothing in the sheet knew that.
Three habits cover it. Write the convention down in a cell, in words. Bound every window with >= the first day and < the next first day, never <= the last. And print the window beside the total, so the dates are something a reader can check rather than something the formula quietly assumes.