Excel 2010 formula for calculating notice period and probationary period

Hello,

Could you help me find a way to calculate a trial period, a notice period...?

Conditions:
If it's an amendment then there's no trial period

If it's a permanent contract (CDI) and the status is empty, then trial period = 2 months unless they had a fixed-term contract (CDD) just before without interruption; in those cases, "trial period to be determined"

If it's a CDI and the status = executive, then trial period = 4 months

If it's a CDD and it's their first contract or if it’s not their first contract but there is an interruption with the last contract, then the calculation is:
1 day / week up to a limit of 10 days, but if it's a CDD for 6 months, then the period = 6 months
example: CDD of 3 months
There are 12 weeks in 3 months, so their trial period is 10 days (limit 10 days)
example: CDD 1 month and 14 days
then trial period = 6 days (because there are 6 weeks, calculation 1 day per week)

If it's a renewed CDD and there is no interruption with their last contract, then there is no trial period

And then I would like to calculate the duration of the notice period as well as the end date of the notice period.
A notice period corresponds to the time an employer has to inform the employee if they want to end their trial period and vice versa, but the employee has fewer constraints, and that's not the part I'm interested in.

Rule:
24 hours before the end of the trial period if the employee's presence is less than 8 days
48 hours before the end of the trial period if the employee's presence is between 8 days and 1 month
2 weeks before the end of the trial period after 1 month of presence
1 month before the end of the trial period after 3 months of presence

What I would like is to have a column that automatically indicates the notice period, whether it's 24h or 48h...
and another column that indicates the deadline for the end of the notice period, but these columns must be updated automatically according to the current date.

Example:
A CDD from 02/02/14 to 01/03/14 = 1 month of work
Trial period = 4 days, so 05/02/14
Duration of notice period: 24h since their trial period is less than 8 days
Deadline for end of notice period: 04/02/14

Example:
A CDD from 03/06/14 to 02/11/14 = 5 months of work
trial period = 10 days, so 12/06/14
Notice period: well, that depends on the current date; for example, if it has been - 8 days since they joined the company and we wish to end their trial period, then we have 24h.
If, based on the current date, it has been more than 8 days but less than 10 days (duration of their trial period), then we have 48h to inform them of the end of their trial period.
So if notice = 24h then deadline for end of notice period = 11/06/14
but if notice = 48h then deadline for end of notice period = 10/06/14

The final goal is for the contracts to be drafted via mail merge.
And obviously, with pivot tables, we can exploit this source for the social report, for statistics...

So if you have any solutions that would be great.

Thank you very much for your help!

Chouchou

I don't know how to attach my Excel table....