Automatic update month when date change based on certain parameters

  • 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!

  • Re: Automatic update month when date change based on certain parameters


    Hi Batman,


    Thanks for the welcome and I think your attached is what I'm looking for!


    Is it possible to amend the formula such that it ignore the year and follow the month instead?


    Using your spreadsheet as example, when I amend the expiry date to 24-02-2015, the billing period shows as 1 because of the year. I will like it to be shown as 3.


    Reason being sometimes people forget to amend the expiry year and if they sort using billing period, they then miss out the policies for a particular month because those with the wrong year will be missed out.


    Sorry for being confusing on the F4 and G4. F is inception date and G is expiry date. I forgot to amend it to B and C to align with the example.


    And many thanks again! You have been a great help!



    Quote from Batman;765306

    Hi wishes21,


    Welcome to the Ozgrid forum.


    I haven't attempted to work out what you currently have in F4 and G4, but does the attached help?

  • Re: Automatic update month when date change based on certain parameters


    Hi,


    The formula can be changed, but I deliberately created it that way for a particular reason.


    My assumption was that, if the billing dates changed by month, then the chances are that they will also change by year, although that may not be correct. I also assumed that your data table would contain expiry dates spanning years. The use of Excel 'Tables' allows you to just add new items onto the end of the table, causing the table to expand with no need to change the formulas that feed off of it.


    Consequently if, at the start of a year you just add to the table the billing dates for the year then there should be no need to delete any data or change any formulas. You could set up the billing dates for several years in advance.


    However, if your billing dates are the same every year, and you would prefer to have calculations as in your last post, please see the attached. The billing dates in the table all have a year of 2000, and the formatting of the cells is changed to not display the year. The calculation in the main table creates a date, to match to the billing dates, of the day and month of the expiry date plus a hard coded year of 2000.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!