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]

try this

Book1
	A	B	C	D
1	Join date	Status	Annual leave @	09/09/2020
2	13/07/2014	NONSUP	7	13.0
3	14/11/2019	SUP	10	8.2
4	12/08/2014	SUP	10	14.0
5	13/07/2018	NONSUP	7	9.0
6	30/04/2011	NONSUP	7	14.0
7	01/12/2010	NONSUP	7	14.0
8	24/11/2017	SUP	10	12.0
9	05/11/2015	NONSUP	7	11.0
10	19/05/2013	SUP	10	14.0
11	18/02/2013	NONSUP	7	14.0
Sheet1

Cell Formulas
Range	Formula
D1	D1	=TODAY()
D2:D11	D2	=IF(($D$1-A2)<=365,C2*($D$1-A2)/365,MIN(14,C2+INT(($D$1-A2)/365)))

 

[ambz123]

Hi Alan,

Thanks for your suggestion. I have tried but for the first year they are only entitled to 7 days or 10 days even completion of their 1st year services. But the formula will auto add 1 day on his completion of 1st year.(As shown on K6) The additional of leave should only be added on their second year of services. How do i change the formula for that.

 

[AlanY]

that should do it

Book1
	A	B	C	D
1	Join date	Status	Annual leave @	12-Sep-2020
2	13-Jul-2014	NONSUP	7	13.0
3	10-Sep-2019	NONSUP	7	7.0
4	12-Aug-2014	SUP	10	14.0
5	13-Jul-2018	NONSUP	7	9.0
6	30-Apr-2011	NONSUP	7	14.0
7	01-Dec-2010	NONSUP	7	14.0
8	24-Nov-2017	SUP	10	12.0
9	05-Nov-2015	NONSUP	7	11.0
10	19-May-2013	SUP	10	14.0
11	18-Feb-2013	NONSUP	7	14.0
Sheet1

Cell Formulas
Range	Formula
D1	D1	=TODAY()
D2:D11	D2	=IF(($D$1-A2)<=2*365,INT(C2*($D$1-A2)/365),MIN(14,C2+INT(($D$1-A2)/365)))

 

[ambz123]

Hi Alan, can i check with you for employee who are less than 3 months are entitled to 0 days leave how do I edit based on the formula? However if they are with us for 4 months and above they are entitled to pro-rated of leave based on their join date. I tried to use the formula on them I still get 1 annual leave which is not correct

 

[AlanY]

try this

Book1
	A	B	C	D
1	Join date	Status	Annual leave @	06/10/2020
2	01/07/2020	NONSUP	7	1
3	01/01/1900	NONSUP	7	14
4	12/08/2014	SUP	10	14
5	13/07/2018	NONSUP	7	9
6	30/04/2011	NONSUP	7	14
7	07/07/2020	NONSUP	7	0
8	24/11/2017	SUP	10	12
9	05/11/2015	NONSUP	7	11
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	=IF(A2>DATE(YEAR($D$1),MONTH($D$1)-3,DAY($D$1)),0,IF(($D$1-A2)<=2*365,INT(C2*($D$1-A2)/365),MIN(14,C2+INT(($D$1-A2)/365))))

 

[ambz123]

Hi Alan, thank you for the solution. It works!!

 

[AlanY]

you're welcome

 

[ambz123]

Hi Alan for instant my staff joined in at 16 Aug 2019 she is entitled to 7 days of leave. But till date she supposed to be receiving a pro-rated leave balance of 7.5 days as of today's date instead of 8 days. Is it possible to apply on the existing formula?

 

[AlanY]

you can take out the int() like this

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

Cell Formulas
Range	Formula
D1	D1	=TODAY()
D2:D11	D2	=IF(A2>DATE(YEAR($D$1),MONTH($D$1)-3,DAY($D$1)),0,IF(($D$1-A2)<=2*365,(C2*($D$1-A2)/365),MIN(14,C2+(($D$1-A2)/365))))