Fiscal Month Formula

[ExcelMarf]

Hi All,

I'm currently trying to work out how to calculate the fiscal month from a date.

For a fiscal year starting April I have this(date sits in column AG)

=IF(MONTH(AG148)>3,MONTH(AG148)-3,MONTH(AG148)+8)

for a fiscal year starting May I have this

=IF(MONTH(AG148)>4,MONTH(AG148)-4,MONTH(AG148)+8)

Both of these work 100%, however I now need to calculate a fiscal month where the fiscal year starts in October

I tried this

=IF(MONTH(AG148)>9,MONTH(AG148)-9,MONTH(AG148)+8)

But the results are not correct, what am I doing wrong?

Thanks in advance

Sam

 

[Boller]

Perhaps for April 1 :-

<TABLE style="WIDTH: 226pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=301><COLGROUP><COL style="WIDTH: 226pt; mso-width-source: userset; mso-width-alt: 11008" width=301><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 226pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64 height=17 width=301>=IF(MONTH(A1)<4,MONTH(A1)+9,MONTH(A1)-3)

And similarly for other fiscal year starting dates.
</TD></TR></TBODY></TABLE>

 

[barry houdini]

This formula would do it

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

format result cell as general

change the 10 depending on start month

 

[ExcelMarf]

Thanks barry, that works a treat.