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

 

[AlanY]

ok, that clarified a bit.
what if someone joined 2 months ago, the leave this year should be 7*4/12 = 2 days?

 

[AlanY]

one more go

Book1
	A	B	C	D	E
1	Join date	Status	Annual leave @	31/12/2020
2	16/08/2019	NONSUP	7	7.5	7.33
3	02/11/2018	NONSUP	7	8	8.08
4	12/08/2014	SUP	10	14	14.00
5	13/07/2018	NONSUP	7	8.5	8.42
6	30/04/2011	NONSUP	7	14	14.00
7	07/07/2020	NONSUP	7	3	2.92
8	24/11/2017	SUP	10	12	12.08
9	05/11/2015	NONSUP	7	11	11.08
10	19/05/2013	SUP	10	14	14.00
11	31/10/2018	NONSUP	7	8	8.17
12	30/10/2020	NONSUP	7	0	0.00
Sheet1

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

 

[ambz123]

ok, that clarified a bit.
what if someone joined 2 months ago, the leave this year should be 7*4/12 = 2 days?

Hi Alan, for any one who joined less than 3 months are not entitled to any leave. But once they are more then 3 months then we will start to pro rate their leave. And continue to add up their leave but pro rated calculation

 

[ambz123]

Hi, I need some help over here. There is a set of new requirements for the following where my leave system has to follow. The condition is as follow:
Now we have to differential via their position for their leave entitlement.