Automatic update month when date change based on certain parameters

[wishes21]

Hi all,

Need help with a formula for this. I have multiple policies with different inception dates and expiry dates.

I have 3 columns A,B,C

Column B has the inception date of the policy, eg: 01/05/2015
Column C has the expiry date of the policy, eg: 30/04/2016

My billing dates every month is different so eg, April can be on 25th and May can be 27th

I want to make it such that in Column A, it automatically populates the month where the policy will be billed based on their expiry date. So in my example above, since the policy is expiring on 30/04/2016, column A should show 5 which stands for May. If the policy is expiring on 24/04/2016, column A should show 4 since April billing date is 25th.

Currently my predecessor is using this formula but it's meant for all billing dates on 25th and I can't figure out how to edit this formula.

=IF(AND(MONTH(F4)=1,DAY(F4)=1,MONTH(G4)=12,DAY(G4)=31),1,IF(ISBLANK(G4),"-",
IF(DAY(G4)>25,MIN(MONTH(G4)+1,12),MIN(MONTH(G4),12))))

As this is a spreadsheet that anyone can add on to, I'm looking for a formula that encompass all 12 months instead so everyone can just use the same formula, and not having to amend the number 25 in above formula each time they key in.

Hope I'm not too confusing.

Many thanks!

[Kevin UK]

Hi

Something like this, if I understand you correctly!

Code: Select all

=IF(DAY(C2)>25,MONTH(EOMONTH(C2,1)),MONTH(EOMONTH(C2,0)))

Or

Code: Select all

=IF(DAY(C2)>25,MONTH(EDATE(C2,1)),MONTH(EDATE(C2,0)))

I have just read your post again, how do you know when the billing date for each month is!

If you have a assumption table, then you could use index/match, vlookup or lookup etc.

Post a example workbook.