Spreadsheets

Financial year column (April to March) from a date in Excel

Build a financial year column from a date in Excel for April to March, with a start-year formula, a 2024-25 label and a quarter number for filtering.

Editorial illustration for Financial year column (April to March) from a date in Excel

For an April to March financial year, the start year of any date is =YEAR(A2)-(MONTH(A2)<4). Dates in January, February and March belong to the year that began the previous April, so the formula subtracts 1 for those months. Add a text label with =YEAR(A2)-(MONTH(A2)<4)&"-"&RIGHT(YEAR(A2)-(MONTH(A2)<4)+1,2), which gives 2024-25 for a date such as 15 January 2025.

How does the formula work?

The comparison MONTH(A2)<4 gives TRUE for January, February and March and FALSE for the other nine months. In arithmetic, Excel treats TRUE as 1 and FALSE as 0. So for 10 April 2025 the formula is 2025 minus 0, which is 2025, and for 15 January 2025 it is 2025 minus 1, which is 2024.

DateStart year formulaLabel
31-Mar-20252025 − 1 = 20242024-25
1-Apr-20252025 − 0 = 20252025-26
15-Jan-20262026 − 1 = 20252025-26
20-Nov-20252025 − 0 = 20252025-26

What is the label formula and why have both?

Keep the start year in one column and the label in another. The start year is a number, which is the safest to use in SUMIFS, filters and sorting. The label is for people. In D2 enter =IF(A2="","",YEAR(A2)-(MONTH(A2)<4)) and in E2 enter =IF(D2="","",D2&"-"&RIGHT(D2+1,2)). The IF guards leave the cell empty for unused rows. Fill both down.

Is there another way to write it?

Yes. =YEAR(EDATE(A2,-3)) moves the date three months back and then takes the year. April 2025 becomes January 2025, which is 2025, and January 2025 becomes October 2024, which is 2024. It gives the same start year and some people find it easier to remember. Pick one and use it everywhere.

How do I use the column?

  • Total one year: =SUMIFS($C$2:$C$500,$D$2:$D$500,2025) for the year starting April 2025.
  • Put the year in a cell, say G1, and use =SUMIFS($C$2:$C$500,$D$2:$D$500,$G$1).
  • Filter the sheet on column E, or add column E to a PivotTable's filter area, and switch years from the dropdown.
  • Combine with a category: =SUMIFS($C$2:$C$500,$D$2:$D$500,$G$1,$B$2:$B$500,"Medical").

Using the numeric start year as the criterion avoids a surprise: a text label such as 2025-26 may be read oddly by SUMIFS, so test it before you rely on it.

How do I get the quarter of the financial year?

=INT(MOD(MONTH(A2)-4,12)/3)+1 returns 1 for April to June, 2 for July to September, 3 for October to December and 4 for January to March. Check July: MONTH is 7, 7 minus 4 is 3, MOD of 3 and 12 is 3, divided by 3 is 1, INT is 1, plus 1 is 2. For January: 1 minus 4 is minus 3, MOD(-3,12) is 9, divided by 3 is 3, plus 1 is 4.

Why do this at all?

India's financial year runs April to March, while statements and salaries run on calendar months. A financial year column lets one log answer both, without splitting files by year. Rules and deadlines for any filing are not covered by this formula, so confirm them with your CA.

To see a particular year's boundaries, write them out: the financial year starting in 2025 runs from DATE(2025,4,1) to DATE(2026,3,31). A SUMIFS with ">="&DATE(2025,4,1) and "<"&DATE(2026,4,1) gives the same total as the helper column. Using both once is a good test that your helper column is correct before you build reports on top of it.

When a sheet stops being enough

SpendixAI offers a library of 25 ready-made reports and a Report builder for filtering and grouping, so a year view does not need helper columns, but if you are comfortable with the sheet, the formula above does the job.

Checklist

  • Real dates, tested with ISNUMBER.
  • Start year in one column, label in another.
  • Checked on 31 March and 1 April, the two dates where mistakes show.
SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports