To turn a date into a fiscal month number where April is 1 and March is 12, use =MOD(MONTH(A2)+8,12)+1. If you prefer something you can read at a glance, =IF(MONTH(A2)>=4, MONTH(A2)-3, MONTH(A2)+9) gives the same answer. Either works in Excel and Google Sheets, and the number then drives sorting and running totals for the April-March year.
What is the formula, and why does it work?
Calendar months run 1 to 12 from January. For the financial year you want to shift the start to April. The IF version says: if the month is April or later, subtract 3 (April 4 becomes 1); otherwise add 9 (January 1 becomes 10). The MOD version adds 8, wraps around at 12 using the remainder, and adds 1 back. Adding 8 rather than subtracting 4 avoids a negative number inside MOD, which some tools handle differently.
Does it give the right answer? A test table
Always test a date formula on edge dates before trusting it. Put these dates in a column and fill the formula down.
| Date | Calendar month | Fiscal month |
|---|---|---|
| 1 April 2025 | 4 | 1 |
| 15 August 2025 | 8 | 5 |
| 31 December 2025 | 12 | 9 |
| 1 January 2026 | 1 | 10 |
| 31 March 2026 | 3 | 12 |
If 31 March does not give 12 and 1 April does not give 1, something is wrong with the formula or the dates, often dates stored as text. A real date is right-aligned in the cell.
How do you get the fiscal year label too?
A month number is half the picture; the year matters for grouping. The financial year that starts in April has a start year equal to the calendar year if the month is April to December, and one less if the month is January to March. Use:
=YEAR(A2)-(MONTH(A2)<4)&"-"&RIGHT(YEAR(A2)-(MONTH(A2)<4)+1,2)
A date of 15 August 2025 gives 2025-26 and 10 February 2026 also gives 2025-26. The comparison MONTH(A2)<4 returns TRUE or FALSE, and the minus sign turns it into 1 or 0.
How do you use the number for a year-to-date table?
Say your data has dates in column A, amounts in column C and the fiscal month in column E. In a summary, list months 1 to 12 in column G and write the year-to-date total in H2:
=SUMIFS($C$2:$C$500, $E$2:$E$500, "<="&G2, $F$2:$F$500, $J$1)
Here column F holds the fiscal year label and J1 holds the year you want, such as 2025-26. Row 3 of the summary, G3 = 3, adds April, May and June. It is a running total with no separate cumulative column needed.
For the single month instead, change the criterion to "="&G2 or just use G2.
Where else does the fiscal month number help?
- Sorting a pivot or chart April to March without a custom list.
- Building a quarter label: =ROUNDUP(E2/3,0) gives 1 for April to June, 2 for July to September, 3 for October to December and 4 for January to March.
- Checking how many months of the year are done: =MAX(E2:E500) on the current year's rows.
- Annualising a number: divide the year-to-date spend by the fiscal month and multiply by 12 for a rough run-rate.
What if your year is not April to March?
Change the number you add. For a year that starts in July, use =MOD(MONTH(A2)+5,12)+1. The rule is: add 12 minus the start month, then 1 more after the MOD. April is month 4, so 12 - 4 = 8.
A closing check
Can you store the month index in a named cell?
Yes. If several sheets use the same year start, put the start month in one cell, say Settings!B1 with 4 for April, and use =MOD(MONTH(A2)-Settings!$B$1+12,12)+1. April gives (4-4+12) mod 12 + 1 = 1 and March gives (3-4+12) mod 12 + 1 = 12. A single setting makes the workbook easy to adapt later.
Before you build more on top, confirm that the sum of the twelve monthly totals equals the total of the whole year's rows. If it does not, look for dates outside the year or text dates. If you later outgrow the sheet, SpendixAI offers ready-made reports and a CSV download, so the same helper column can be rebuilt in a minute.
Useful ideas start with a clearer picture of your own spending.
Explore your reports

