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!