Extract financial Year Number

[howard]

My financial year starts in Oct (Period 1) and ends in Sept (Period 12)


I have the month and year in A2. The current financial month in A2 is June 2017 (I use dd/mm/yyyy date formatting)


I tried to use

 =CHOOSE(MONTH(A2),10,11,12,1,2,3,4,5,6,7,8,9)

However it returns 3 and should return 9


It would be appreciated if someone could kindly assist me

 

[Tetra201]

Try this:

=MOD(MONTH(A2)-10,12)+1

 

[howard]

Thanks, Formula works perfectly

Explain how this function works. Never used it before

 

[Tetra201]

MOD "returns the remainder after number is divided by divisor. The result has the same sign as divisor."

So, for June 2017 in cell A2:
MONTH(A2) equals to 6
6-10 equals to -4
MOD(-4,12) equals to 8
8+1 equals to 9

 

[howard]

Thanks for the explanation