Spreadsheets

Fiscal year label from a transaction date in Google Sheets

Create an FY 24-25 style fiscal year label for every transaction in Google Sheets with IF, MONTH, TEXT and ARRAYFORMULA, with blank rows handled.

Editorial illustration for Fiscal year label from a transaction date in Google Sheets

To label each transaction with its April to March financial year in Google Sheets, put one formula in the header cell: ={"FY";ARRAYFORMULA(IF(A2:A="","","FY "&TEXT(MOD(IF(MONTH(A2:A)<4,YEAR(A2:A)-1,YEAR(A2:A)),100),"00")&"-"&TEXT(MOD(IF(MONTH(A2:A)<4,YEAR(A2:A)-1,YEAR(A2:A))+1,100),"00")))}. A date in February 2025 becomes FY 24-25, and a date in May 2025 becomes FY 25-26.

What does each part of the formula do?

  • IF(MONTH(A2:A)<4,YEAR(A2:A)-1,YEAR(A2:A)) picks the year in which the financial year started. January to March belong to the year before.
  • MOD(...,100) keeps the last two digits, so 2024 becomes 24.
  • TEXT(...,"00") makes sure a single digit still shows as two, so 2009 would read 09.
  • The second MOD adds 1 to the start year to get the closing year, so the label reads 24-25.
  • IF(A2:A="","",...) leaves the cell blank where there is no date.

The curly brackets and semicolon put the word FY in row 1 and stack the labels below it. Everything from E2 down must be empty, or Sheets will show a #REF! error.

Is there a shorter version?

Add a helper column for the start year and build the label from it. In E1 enter ={"FY start";ARRAYFORMULA(IF(A2:A="","",IF(MONTH(A2:A)<4,YEAR(A2:A)-1,YEAR(A2:A))))}. In F1 enter ={"FY";ARRAYFORMULA(IF(E2:E="","","FY "&TEXT(MOD(E2:E,100),"00")&"-"&TEXT(MOD(E2:E+1,100),"00")))}. Two short formulas are easier to read than one long one, and the numeric start year is the safest column for SUMIFS.

Worked example with made-up dates

DateStart yearLabel
28-Mar-20252024FY 24-25
1-Apr-20252025FY 25-26
12-Dec-20252025FY 25-26
5-Feb-20262025FY 25-26

Notice that the last row, in February 2026, is still FY 25-26. Only a date on or after 1 April 2026 would start FY 26-27.

How do I list every financial year and total each one?

  1. In H2 enter =SORT(UNIQUE(F2:F)). Because F2:F includes blanks, you will see an empty row, which you can ignore, or use =SORT(UNIQUE(FILTER(F2:F,F2:F<>""))).
  2. Beside the first label, in I2, enter =SUMIFS($C$2:$C$500,$F$2:$F$500,H2).
  3. Fill I2 down to match the list.

If you would rather not copy formulas down, a QUERY does the grouping in one go: =QUERY(A1:F,"select F, sum(C) where F is not null group by F label sum(C) 'Total'",1). Replace the columns with your own.

What if the label stays blank or says an error?

  • A blank label means the date cell is empty, or the formula column is not where the array can fill.
  • Labels that look wrong for text dates: TEXT and MONTH on text can fail. Check with =ISNUMBER(A2).
  • A #REF! error means something is typed in the column below row 1. Clear it.
  • If commas give a parse error, your locale may need semicolons between arguments. Check File, then Settings.

The same pattern carries over to a half-year or quarter label. For the quarter, =ARRAYFORMULA(IF(A2:A="","","Q"&(INT(MOD(MONTH(A2:A)-4,12)/3)+1))) returns Q1 for April to June up to Q4 for January to March. Combine it with the FY label, for example FY 25-26 Q2, when you want a quarter-wise view across several years.

To look at one year at a time, add a dropdown in H1 listing the labels, and use =SUM(FILTER($C$2:$C$500,$F$2:$F$500=$H$1)) to total it, or =FILTER($A$2:$F,$F$2:$F=$H$1) to list that year's rows. Pick a different label and the answer changes. This keeps one master log with every year in it, which makes comparing this year with last year a matter of switching the dropdown.

When a sheet stops being enough

SpendixAI covers reports across a period through its Report builder and the ready-made report library, and its CSV download hands the numbers back to a sheet if you need them there. If your own formulas work, you may not need it for this.

Test before you trust

  • Enter dates for 31 March and 1 April and check the labels change.
  • Confirm blank rows stay blank after you add and delete a row.
  • Compare one year's total with a manual sum of a filtered view.
SPENDIXAI JOURNAL

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

Explore your reports