0

Using the below formula to calculate the Financial Year from the Date (G) column:

=IF(MONTH(G2)>4,YEAR(G2)&"-"&YEAR(G2)+1,YEAR(G2)-1&"-"&YEAR(G2))

It works for that specific cell - G2 but I'm wanting to apply the same formula to the entire 'Tax Year' column.

image

Tried pulling the box down on right lower corner but this clears my formatting (colours of row etc) and means that the formula won't automatically be applied when I insert a new row.

vimuth
  • 5,064
  • 33
  • 79
  • 116
Louise Emily
  • 11
  • 2
  • Update: Did find this works for every cell if I paste into the cells individually but can't figure out how to just apply it to every column: =IF(MONTH(G2:G)>4,YEAR(G2:G)&"-"&YEAR(G2:G)+1,YEAR(G2:G)-1&"-"&YEAR(G2:G)) – Louise Emily Jan 29 '23 at 09:53

1 Answers1

0

Try:

=INDEX(IF(LEN(G2:G),LAMBDA(z,IF(MONTH(G2:G)>4,z&"-"&z+1,z-1&"-"&z))(YEAR(G2:G)),))
rockinfreakshow
  • 15,077
  • 3
  • 12
  • 19