Calculating the Next Due Date from Two Different Dates

Hi Everyone, I am currently working on a spreadsheet for someone at work and require a little help as to how best format and organise the cells to minimise the size when printed and the amount of input cells required to complete by the user. (I think I might need to upload the spreadsheet to anyone who might be willing to help in order for them to get a better understanding).

These are the headers for the cells I have;

‘Date joined Company’, ‘Date of last appraisal’, ‘Next Appraisal Due’ and ‘Status’.

The first appraisal needs to be completed within 6 months of the person first joining the company and then on an annual basis there after. I am struggling with what type of formula I would use to calculate this.  I think I might need at least one more column to help with the calculations. (Sorry I’m not sure how to upload a screen shot of the spreadsheet so you guys can get a general idea of what I’m talking about)

I would be most apprieciative if anyone could assist as I’m racking my brains as to the most efficient way of making this work.

Best Regards,

Matt

4 thoughts on “Calculating the Next Due Date from Two Different Dates

  1. andrew walker Post author

    Hi there

    For my formula I have cell A1 for Date Joined, B1 for Last Appraisal, C1 Next Appraisal and D1 for Status. Row 2 contains the first line of dates.
    Try putting this formula into the column for Next Appraisal (C2)…

    =IF(AND(TODAY()>A2,ISBLANK(B2)),A2+180,B2+365)

    What this formula does is check if Date Joined is less than today and there has been no other Appraisal in cell B2. If this is the case it sets the next appraisal date as A2 + 180 days. Otherwise, it adds 365 days to the date in C2.

    Next, you could use this formula for the Status column.

    =IF(C2-NOW()<0,"OVERDUE"&" ("&ABS(ROUND(C2-NOW(),0))&")",C2-NOW())

    This formula will tell you how many days are left before the next appraisal is due. If the date has been missed it will say OVERDUE and in brackets it will tell you by how many days it is overdue.

    Hopefully this is what you were after.

    Andy

  2. Matthew

    Hi Andy,
    That works fantastically well, thank you so much. I just have a couple more questions if you don’t mind looking for me.

    In the status column formula, if I wanted to do a similar thing to the Overdue () and have it say OK and then the number of days left before the next appraisal how would I amend the formula to reflect this?

    Also what would I need to add to each formula in order for the cells to stay blank if there is no data in the other cells but still work if something was inputted. The reason for this is because I want to copy the formulas down to about the 15th row and lock them so the formulas cannot be tampered with (so in effect I can send out a blank worksheet to be filled in without the worry of anyone having to copy formulas down).

    Hope this makes sense. Again many thanks for your help. Your an Excel GOD!

    Matt

  3. andrew walker Post author

    Hi Matt

    No worries…

    Try these two formulae. 🙂

    For the Next Appraisal Column
    =IF(AND(ISBLANK(A2),ISBLANK(B2)),"",IF(AND(TODAY()>A2,ISBLANK(B2)),A2+180,B2+365))

    For the Status Column
    =IF(C2="","",IF(C2-NOW()<0,"OVERDUE"&" ("&ABS(ROUND(C2-NOW(),0))&")","OK "&ROUND(C2-NOW(),0)))

    Let me know how you get on with those.

    Cheers

    Andy

  4. Matthew

    Hi Andrew,
    Many thanks for that, it all works spot on now.

    This may not be something that you’re interested in but I am currently looking for someone to take a look at an important Excel workbook that looks after the hours of work/rest onboard our different sea going vessels that our company owns. The spreadsheet is a little complicated as it’s looking at a lot of different things at once. The proceedure has specific rules and guidelines in which the seafarers have to abide by in order to comply with regulations.
    I have actually taken a spreadsheet that was designed by a german company and tried to make it fit our specific needs as per the Quality Managers request however it doesnt seem to be calculating the hours correctly.
    Obviously this will require a little bit more involvement on your part so if you felt you were up to the task then in no way would I expect you to do this for free. I’ve already discussed this with my MD and he is willing to pay someone in order to get this sorted out ASAP as it’s pretty important (It could potentially stop a ship from running). If you are interested then feel free to email me on mo@whitaker-tankers.co.uk so I could maybe send you over the regulations and the spreadsheet so that you can get your head around it.
    Again many thanks for your help with the previous question I posted, it’s very much apprieciated.
    Cheers
    Matt

Leave a Reply

Your email address will not be published. Required fields are marked *