Tuesday, 25 March 2014

Financial Year Formulae in Google Sheets

A lot of my reports involve breaking down data into financial years, April to March.
Switching to Google sheets from excel has meant that I have had to find a new formulas and methods to do this, which are documented here.

Simple method...

Assuming your date is in cell A3 the following formula will work fine

=IF(MONTH(A3)<4, YEAR(A3)-1 &"/"&Right(YEAR(A3),2), YEAR(A3) &"/"&right(YEAR(A3)+1,2))

end result would be shown as for example  


DateFinancial Year
01/04/20132013/14
02/04/2013
03/04/2012

More powerful method...

This is all well and good - but Google sheets have a fantastic ability to use an array, entering in a single formula in a cell and the sheet then automatically calculating for the whole column.

=ArrayFormula(IF(MONTH(A3:A)<4, YEAR(A3:A)-1 &"/"&Right(YEAR(A3:A),2), YEAR(A3:A) &"/"&right(YEAR(A3:A)+1,2)))

End result

DateFinancial Year
01/04/20132013/14
02/04/20132013/14
03/04/20122012/13
1899/00
1899/00
1899/00
1899/00


Even better method...


As you can see from the example above, the array fills down all cells, giving a load of unwanted results where there is no date in column A.
The solution is to include another IF, which instructs only to fill down those cells where there is data in column A.

=ArrayFormula(IF(len(A3:A),IF(MONTH(A3:A)<4, YEAR(A3:A)-1 &"/"&Right(YEAR(A3:A),2), YEAR(A3:A) &"/"&right(YEAR(A3:A)+1,2)),iferror(1/0)))

DateFinancial Year
01/04/20132013/14
02/04/20132013/14
03/04/20122012/13
04/04/20132013/14
05/04/20102010/11
06/04/20112011/12
07/04/20132013/14
08/04/20132013/14
09/04/20132013/14

Hope this is of use, especially to my NIHR colleagues!

No comments:

Post a Comment