Help with formula

[Guest]

Hi,

I have the following spreadsheet:

Cell A1: Last Month In Financial Year
Cell B1: 3

Cells A3 - A999 : various dates in the format dd/mm/yyyy

What I am trying to do is extract the Period number for each date. The
Period is related to the Month of each date, but varies depending on the
value in Cell B1.
So, using the above example, Cell B1 = 3 which mean that the last month in a
financial year is MARCH. Therefore, if a date in Cells A3-A999 is in April,
then the associated period = 1, if in May then = 2 etc.

Would like to avoid writing a custom function as I am sure that there must
be a way to use existing formulas to calculate the periods.

Suggestions?

 

[Guest]

Sorry, please ignore this one. I have resolved it...

=IF(MONTH(A2)-$B$1<=0,12+(MONTH(A2)-$B$1),MONTH(A2)-$B$1)

 

[David Biddulph]

Or =MOD(MONTH(A2)-$B$1-1,12)+1 ?

 

[Guest]

Thanks David,
Yours is far neater.
Out of curiosity though, when Month(A2) =1 (ie: January) and $B$1 = 3 (ie:
March), the formula is:

Mod(1-3-1,12)+1
or Mod(-3,12)+1

so -3 Mod 12 = 9??????????

 

[David Biddulph]

Yes. The only reason for having the -1 inside the MOD() function and +1
outside it is to cope with March, where otherwise you'd have =MOD(0,12)
which would give 0 and I assumed you wanted 12.

You'll see an explanation of the MOD() function in Excel's help.

 

[Guest]

Thanks again David.

Yes. The only reason for having the -1 inside the MOD() function and +1
outside it is to cope with March, where otherwise you'd have =MOD(0,12)
which would give 0 and I assumed you wanted 12.

You'll see an explanation of the MOD() function in Excel's help.