Complicated Formula for Calculating Notice Period

[Chris_010101]

Hi All,

So, the company who I work for do this really complicated thing whereby if you're being made redundant but you go on a secondment, they pause your notice period whilst you're on the secondment and then reinstate the remainder of your notice period, so it runs concurrently until the end of your secondment (making the end of the secondment your last day with the company).

Example:

Date of my formal notice letter telling me I have four month's notice to work - 01/05/2020
Date my secondment begins (and notice is paused) - 03/06/2020
Notice I've already worked - 1 month and 3 days
Notice remaining - 2 months and 27 days (of my 4-month notice period)
Expected end of secondment - 30/11/2020
Notice period reinstatement date (30/11/2020 minus 2 months and 27 days) = 03/09/2020

In simple terms, work a portion of notice, pause notice, reinstate the remainder to run up until secondment end date.

Question is, how can this be calculated on excel to make my colleague's lives easier? I've gotten all the way to "notice remaining" on the attached but I'm completely stuck. Hoping for someone who is an excel expert but also understands HR practices with UK notice periods to see if they can solve this.

P.S, this will be a general calculator, so it will need to calculate from the "employee notice (months)" cell as this will change depending upon the employee's grade etc.

Regards
Chris

[Estevaoba]

Maybe:

Please Login or Register  to view this content.

[Chris_010101]

Maybe:

Please Login or Register  to view this content.

Hey Estevaoba,

Thank you so much!

It works and works well. I was able to replace the digit “4” with a reference to the notice period cell, so whatever notice period is entered, it will calculate. This will be brilliant for use as a general calculator and will score me some brownie points with colleagues, so thank you again.

However, where one problem is solved, another is presented. I am now unable to make the “date notice reinstated” cell calculate. Is there a formula I can use for this?

Help is always greatly appreciated.

Kind regards
Chris

[Estevaoba]

You're welcome, Chris.
Try this in G5:
=B10-DATEDIF(B6,EDATE(B5,4),"D")

[Chris_010101]

You're welcome, Chris.
Try this in G5:
=B10-DATEDIF(B6,EDATE(B5,4),"D")

Thank you again, Estevaoba!

Your help is very much appreciated.

[Chris_010101]

You're welcome, Chris.
Try this in G5:
=B10-DATEDIF(B6,EDATE(B5,4),"D")

Hello again,

I’ve been testing the calculator today but found it doesn’t work for some variables. For instance, if I put 1 September 2020 in both the “original notice date” and “date notice paused” cells, the “date secondment ends”. cell is 5 March 2021, it returns 3 November 2020, which is incorrect. 4 months back from 5 March 2021 is 5 November 2020.

Regards
Chris

[JeteMc]

Please try the following in cell G5:

Please Login or Register  to view this content.

Note that this assumes cell B3 is the notice period cell mentioned in post #3.
Let us know if you have any questions.

[Chris_010101]

Thanks JeteMC and all who responded.

I managed to get an excel expert in the company to have a look at it and with a few slight tweaks to the formula, it's been up and running for a number of months now.

Really glad there's a stream of helpful people like yourselves out there!!

All the best,
Chris

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

[JeteMc]

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.