Spreadsheets

Add an April to March financial year column to your transactions

Add an April to March financial year column in Excel: one formula labels dates as FY 2023-24, plus a quarter column tested around 31 March.

Editorial illustration for Add an April to March financial year column to your transactions

To tag dates with an April to March financial year, use a start-year helper, =YEAR(A2)-(MONTH(A2)<4), and then a label such as FY 2023-24. Dates from January to March belong to the year that began the previous April.

The formulas

Assume transaction dates in column A, starting on row 2. In B2, the year in which the financial year began:

  • B2: =YEAR(A2)-(MONTH(A2)<4)
  • C2: ="FY "&B2&"-"&RIGHT(B2+1,2)
  • D2: =INT(MOD(MONTH(A2)-4,12)/3)+1

The comparison MONTH(A2)<4 is TRUE for January, February and March, and Excel treats TRUE as one in arithmetic, so those months subtract one from the calendar year. B2 is the start year, C2 builds a label such as FY 2023-24, and D2 gives the quarter of the financial year: 1 for April to June, 2 for July to September, 3 for October to December and 4 for January to March.

Does it behave at the year boundary?

Test the edges. This is where a formula goes wrong, so put these dates in a test block and compare:

DateStart yearLabelQuarter
31 March 20242023FY 2023-244
1 April 20242024FY 2024-251
30 June 20242024FY 2024-251
1 October 20242024FY 2024-253
1 January 20252024FY 2024-254

Walk through one by hand. For 1 January 2025 the month is 1, which is below 4, so the start year is 2025 minus 1, which is 2024. The label is FY 2024-25. The quarter: MONTH minus 4 is minus 3, MOD(-3,12) is 9, divided by 3 is 3, and INT of 3 plus 1 is 4. That matches the table.

Why not just use the calendar year?

Because an Indian household's year is not the calendar year. Salary revisions, insurance renewals, tax paperwork and many business accounts follow April to March, so a January to December total splits one financial year across two. A label column lets a pivot table or a SUMIFS answer the question in the form you are asked it.

Total by financial year

  • With the label in C: =SUMIFS($E$2:$E$500,$C$2:$C$500,"FY 2023-24") where E holds amounts.
  • In a pivot table, put the label in Rows and the quarter in Columns for a four-quarter view.
  • Compare two years side by side by placing both labels in a small table and pointing SUMIFS at each.

Say a hypothetical spend of ₹12,000 on 15 March 2024, ₹9,500 on 2 April 2024 and ₹4,000 on 30 March 2024. FY 2023-24 gets 12,000 plus 4,000, which is 16,000, and FY 2024-25 gets 9,500.

For filtering, it helps to know the exact first and last day of a financial year. From the start year in B2 you can build both: =DATE(B2,4,1) for the opening day and =DATE(B2+1,3,31) for the closing day. For a start year of 2023 these give 1 April 2023 and 31 March 2024, which you can feed straight into a SUMIFS with greater-than-or-equal and less-than-or-equal conditions on the date column.

What if the dates are still text?

MONTH and YEAR need real dates. If B2 shows an error, run =ISNUMBER(A2) and convert the dates first. A text date read in the wrong order will put some rows in the wrong financial year without any visible error, especially around March and April, so check a few with the statement.

If you share the file with a family member or an accountant, write the formula's logic in a note beside the column. A label like FY 2023-24 is only trustworthy when the reader knows it was built from April to March.

Closing checklist

  1. Test the five boundary dates above in your own sheet.
  2. Add a note at the top saying the year runs April to March.
  3. Check that the minimum and maximum labels match your statement range.
SPENDIXAI JOURNAL

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

Explore your reports