Leave entitlement

[ambz123]

Hi,

I am task to create a leave management that auto calculates he pro-rated leave entitlement based on the following condition.

If the employee is SUP he will be entitled to 10 days of annual leave but it has to be pro-rated in accordance to his join date and every additional year he work we will add 1 day till max is 14 days.
While if the employee is NONSUP he will be entitled to 7 days of annual leave but same thing, the 7 days will be pro-rated in accordance to his join date when he first join the company. Every additional year he work with us, we will add 1 day leave till max is 14 days. Hope this clarifies the explanation.

Need some help in this.

Thank you.

Quote Reply
Report

 

[ambz123]

Hi Alan i tried the formula to removed the Int but the answer is still not correct.
For the example below her leave suppose to be 7.5 instead of 8.2. How i get 7.5 is i take no. completed months/ 12 * 7

 

[AlanY]

I've amended the formula's pro-rata based on months

Book1
	A	B	C	D
1	Join date	Status	Annual leave @	14/10/2020
2	16/08/2019	NONSUP	7	7.6
3	01/01/1900	NONSUP	7	14
4	12/08/2014	SUP	10	14
5	13/07/2018	NONSUP	7	9.3
6	30/04/2011	NONSUP	7	14
7	07/07/2020	NONSUP	7	1.8
8	24/11/2017	SUP	10	12.8
9	05/11/2015	NONSUP	7	11.9
10	19/05/2013	SUP	10	14
11	18/02/2013	NONSUP	7	14
Sheet1

Cell Formulas
Range	Formula
D1	D1	=TODAY()
D2:D11	D2	=ROUND(IF(DATEDIF(A2,$D$1,"M")<3,0,IF(DATEDIF(A2,$D$1,"Y")<2,(C2*(DATEDIF(A2,$D$1,"M"))/12),MIN(14,C2+((DATEDIF(A2,$D$1,"M"))/12)))),1)

 

[AlanY]

and this round to half day

Book1
	A	B	C	D
1	Join date	Status	Annual leave @	14/10/2020
2	16/08/2019	NONSUP	7	7.5
3	01/01/1900	NONSUP	7	14
4	12/08/2014	SUP	10	14
5	13/07/2018	NONSUP	7	9.5
6	30/04/2011	NONSUP	7	14
7	07/07/2020	NONSUP	7	2
8	24/11/2017	SUP	10	13
9	05/11/2015	NONSUP	7	12
10	19/05/2013	SUP	10	14
11	18/02/2013	NONSUP	7	14
Sheet1

Cell Formulas
Range	Formula
D1	D1	=TODAY()
D2:D11	D2	=ROUND(IF(DATEDIF(A2,$D$1,"M")<3,0,IF(DATEDIF(A2,$D$1,"Y")<2,(C2*(DATEDIF(A2,$D$1,"M"))/12),MIN(14,C2+((DATEDIF(A2,$D$1,"M"))/12))))/0.5,0)*0.5

 

[joeu2004]

and this round to half day
[....]
=ROUND(IF(DATEDIF(A2,$D$1,"M")<3,0,IF(DATEDIF(A2,$D$1,"Y")<2,(C2*(DATEDIF(A2,$D$1,"M"))/12),MIN(14,C2+((DATEDIF(A2,$D$1,"M"))/12))))/0.5,0)*0.5

Without vetting the DATEDIF calculations, a simpler form is:

=MROUND(IF(DATEDIF(A2,$D$1,"M")<3, 0, IF(DATEDIF(A2,$D$1,"Y")<2, C2*DATEDIF(A2,$D$1,"M")/12, MIN(14, C2+DATEDIF(A2,$D$1,"M")/12))), 0.5)

Note: I usually deprecate the use of MROUND with a decimal fraction in the second parameter, due to binary arithmetic anomalies. But this should work find because the second parameter

 

[ambz123]

Hi Both,

thanks a lot for the suggestion. But i think for this guy the formula doesnt fetch the correct computation.

He has work for 23 months which is nearly 2years but his leave entitlement shows 13.5 but it should 7.5 instead.

 

[ambz123]

Hi Both,

thanks a lot for the suggestion. But i think for this guy the formula doesnt fetch the correct computation.
View attachment 24234
He has work for 23 months which is nearly 2years but his leave entitlement shows 13.5 but it should 8 instead.

 

[AlanY]

Col D rounded to half day of Col E

with joeu2004's suggestion of the MROUND()

Book1
	A	B	C	D	E
1	Join date	Status	Annual leave @	15/10/2020
2	16/08/2019	NONSUP	7	7	7.083333
3	02/11/2018	NONSUP	7	8	7.916667
4	12/08/2014	SUP	10	14	14
5	13/07/2018	NONSUP	7	8.5	8.25
6	30/04/2011	NONSUP	7	14	14
7	07/07/2020	NONSUP	7	2	1.75
8	24/11/2017	SUP	10	12	11.83333
9	05/11/2015	NONSUP	7	11	10.91667
10	19/05/2013	SUP	10	14	14
11	18/02/2013	NONSUP	7	13.5	13.58333
Sheet1

Cell Formulas
Range	Formula
D1	D1	=TODAY()
D2:D11	D2	=MROUND(IF(DATEDIF(A2,$D$1,"M")<3,0,IF(DATEDIF(A2,$D$1,"Y")<1,(C2*(DATEDIF(A2,$D$1,"M"))/12),MIN(14,C2+((DATEDIF(A2,$D$1,"M")-12)/12)))),0.5)
E2:E11	E2	=IF(DATEDIF(A2,$D$1,"M")<3,0,IF(DATEDIF(A2,$D$1,"Y")<1,(C2*(DATEDIF(A2,$D$1,"M"))/12),MIN(14,C2+((DATEDIF(A2,$D$1,"M")-12)/12))))

 

[ambz123]

Hi Alan, i tried your formula, i got the right answer for the guy that join in 2/11/2018 however, i still get an incorrect answer for the one whom join in at 16/08/2019. She should be getting 7.5days of leave

 

[AlanY]

for 7.5 days of leave she needed to be joined 18 months ago (1/2 day for the extra 6 months).
the leave calculation should be 7 + (13-12)/12 = 7.1 days, isn't that right?

 

[ambz123]

Hi Alan, for her case we take 16 Aug 2019 to 16 Aug 2020 = 7 days
so 16 Aug 2020 onwards
the YTD for Year 2020 will be 7.5 days and from Aug till Dec is about 4 mths so we will pro-rate accordingly