EXCEL - Automatic calculation of end of contract and probation period
Solved
Laudme
Posted messages
3
Status
Membre
-
moimeme -
moimeme -
Hello everyone,
I’m trying to automate as much as possible the employee database to also allow for the automation of our contracts. I’m managing with Excel but I don’t master many formulas and I’m stuck...
Basically, I’m looking to create a formula to get the end date of the probation period and the end date of the contract.
1) End of probation period
For the probation period, due to our activity, and since there is always 1 month of probation, it’s just a matter of counting one month.
• Example: Start date on 07/04/2020 → End of probation period: 07/05/2020
And it must be something really simple but I don’t know the formula to add exactly one month.
2) End of contract
Our fixed-term contracts last about 6 months (end of the month), I know the end of month formula which works very well, however, I have a small problem because... for example, let’s take APRIL 2020:
• When it starts between the 1st and the 10th of the month, they will complete the full month + 5 months.
Example: 07/04/2020 → End on 30/09/2020
• When it starts between the 10th and the last day of the month, they will complete 6 months.
Example: 12/04/2020 → End on 31/10/2020
I don’t know if you’re following me... But when I use the formula =EOMONTH(Date;6), it skews the result for those who started in the first 10 days of the month, so I’d like to try a “If between the 1st and 10th day of the month then EOM +5 otherwise +6” function but I’m not quite sure how to go about it.
Would there be a kind soul to give me some advice? ;)
I hope I was clear, otherwise don’t hesitate to ask me to rephrase or for any further information.
Looking forward to hearing from you.
Best regards.
I’m trying to automate as much as possible the employee database to also allow for the automation of our contracts. I’m managing with Excel but I don’t master many formulas and I’m stuck...
Basically, I’m looking to create a formula to get the end date of the probation period and the end date of the contract.
1) End of probation period
For the probation period, due to our activity, and since there is always 1 month of probation, it’s just a matter of counting one month.
• Example: Start date on 07/04/2020 → End of probation period: 07/05/2020
And it must be something really simple but I don’t know the formula to add exactly one month.
2) End of contract
Our fixed-term contracts last about 6 months (end of the month), I know the end of month formula which works very well, however, I have a small problem because... for example, let’s take APRIL 2020:
• When it starts between the 1st and the 10th of the month, they will complete the full month + 5 months.
Example: 07/04/2020 → End on 30/09/2020
• When it starts between the 10th and the last day of the month, they will complete 6 months.
Example: 12/04/2020 → End on 31/10/2020
I don’t know if you’re following me... But when I use the formula =EOMONTH(Date;6), it skews the result for those who started in the first 10 days of the month, so I’d like to try a “If between the 1st and 10th day of the month then EOM +5 otherwise +6” function but I’m not quite sure how to go about it.
Would there be a kind soul to give me some advice? ;)
I hope I was clear, otherwise don’t hesitate to ask me to rephrase or for any further information.
Looking forward to hearing from you.
Best regards.
Liens connexes:
- Excel, tax calculation
- Do you know of any free online hair simulation tools?
- Excel 2010 formula for calculating notice period and probationary period
- convert hours to hundredths in Excel
- Automatic calculation of interview dates and the end of the trial period
- Generate all possible EuroMillions combinations in Excel
3 réponses
Hello,
I would like to follow up on your initial request.
Your probation period is 1 month, there's no problem with that.
However, you stated:
Example: Start date on 07/04/2020 → End of probation period: 07/05/2020.
This is incorrect; the probation period is from 07/04 to 06/05 and not until 07/05, because otherwise it would amount to 1 month and 1 day, and the contract can be reclassified as a permanent contract if you terminate the probation period on 07/05.
Indeed, it is one month worked that starts on the morning of 07/04 and ends on the evening of 06/05.
Best regards,
HR Manager
I would like to follow up on your initial request.
Your probation period is 1 month, there's no problem with that.
However, you stated:
Example: Start date on 07/04/2020 → End of probation period: 07/05/2020.
This is incorrect; the probation period is from 07/04 to 06/05 and not until 07/05, because otherwise it would amount to 1 month and 1 day, and the contract can be reclassified as a permanent contract if you terminate the probation period on 07/05.
Indeed, it is one month worked that starts on the morning of 07/04 and ends on the evening of 06/05.
Best regards,
HR Manager
Pay close attention to the dates when setting up a contract.