Leave Entitlement to count pro-rated leave based on joined date.

[ambz123]

Hi All, would like to ask how do i change the current formula to achieved the pro-rated calculation based on the following condition:

1. Job Code : M
Less than 2 year = 16 AL
2 years but less than 5 years = 18 AL
More than 5 years = 20 AL

2. Job Code: ESO

Less than 2 year = 14 AL
2 years but less than 5 years = 16 AL
More than 5 years = 18 AL

3. Job Code: TFO

Less than 2 year = 10 AL
2 years but less than 5 years = 12 AL
More than 5 years = 16 AL

However, it has to be pro-rated accordingly to joined date till end of year 31/12/2021 calculation.

If less than 3 months is 0 AL

The table below is the formula im using but i cannot achieved the correct figures.

The formula i used is No. of Leave entitlement+ (No. of months joined till end of year-12)/12 = 17

But the formula gives me 17.5 instead. Need some help with that.

MY - Leave Management.xlsm
	A	B	C	D	E	F	G	H		J	K	L	M	N
1	ENTER EMPLOYEE DATA
2				1/28/2021	12/31/2021
3							As of	As of						As of
4		Required		Required			1/28/2021	31/12/21						1/28/2021
5	EMP ID	EMPLOYEE NAME	CITIZEN CODE	JOIN DATE	POSITION	JOB Code	Actual months joined	No. of months joined		HL Entitlement	MC Entitlement	YE AL Entitlement	Current AL Entitlement	Current Accrued AL
6	EPSB 001	Gan An Hean	MAL	12-Jul-2019	General Manager	M	18	29		60	14	18	16	17.5
7	EPSB 002	Nislani Bin Naslin	MAL	01-Sep-2019	Mould Fabricator	TFO	16	27		60	14	12	10	11.5
8	EPSB 003	Mohammad Zamir Bin Abdul Jalal	MAL	02-Sep-2019	Technician	TFO	16	27		60	14	12	10	11.5
9	EPSB 004	Muhammad Syafiq Ezzuddin Bin Zulkefeli	MAL	26-Sep-2019	Technician	TFO	16	27		60	14	12	10	11.5
10	EPSB 005	Muhamad Hafis Bin Jumhat	MAL	10-Sep-2019	Engineer	ESO	16	27		60	14	16	14	15.5
11	EPSB 006	Mohd Fazwan Bin Shahad	MAL	18-Sep-2019	Supervisor	ESO	16	27		60	14	16	14	15.5
12	EPSB 007	Mohd Azizi Bin Awang Su	MAL	26-Sep-2019	Batcher	TFO	16	27		60	14	12	10	11.5
13	EPSB 008	Muhammad Azeem Bin Nordin	MAL	07-Oct-2019	Technician	TFO	15	26		60	14	12	10	11.0
14	EPSB 009	Muhammad Azrie Bin Sarif	MAL	07-Oct-2019	Technician	TFO	15	26		60	14	12	10	11.0
15	EPSB 010	Koh Wei Mui	MAL	01-Nov-2019	Office Executive	ESO	14	25		60	14	16	14	15.0
16	EPSB 011	Kong Wei Sheng	MAL	01-Nov-2019	QS cum Draftsman	ESO	14	25		60	14	16	14	15.0
EMPLOYEES

Cell Formulas
Range	Formula
D2	D2	=TODAY()
E2	E2	=DATE(YEAR(D2),12,31)
G4,N4	G4	= TODAY()
G6:G16	G6	=DATEDIF([@[JOIN DATE]],TODAY(),"m")
H6:H16	H6	=DATEDIF(D6,DATE(2021,12,31),"m")
K6:K16	K6	=IF(I6<2,"14",IF(I6<5,"18",IF(I6<6,"22",IF(I6>6,"22","22"))))
L6:L16	L6	=IF(H6<3,0,IF(F6="M",IF(H6<24,16,IF(H6<=60,18,20)),IF(F6="ESO",IF(H6<24,14,IF(H6<=60,16,18)),IF(F6="TFO",IF(H6<24,10,IF(H6<=60,12,16)),IF(H6<24,8,IF(H6<=60,10,12))))))
M6:M16	M6	=IF(G6<3,0,IF(F6="M",IF(G6<24,16,IF(G6<=60,18,20)),IF(F6="ESO",IF(G6<24,14,IF(G6<=60,16,18)),IF(F6="TFO",IF(G6<24,10,IF(G6<=60,12,16)),IF(G6<24,8,IF(I6<=60,10,12))))))
N6	N6	=ROUND(IF(DATEDIF([@[JOIN DATE]],$E$2,"M")<3,0,IF(DATEDIF([@[JOIN DATE]],$E$2,"Y")<1,([@[Current AL Entitlement ]]*(DATEDIF([@[JOIN DATE]],$E$2,"M"))/12),MIN(20,[@[Current AL Entitlement ]]+((DATEDIF([@[JOIN DATE]],$E$2,"M")-12)/12))))/0.5,0)*0.5
N12:N14,N7:N9	N7	=ROUND(IF(DATEDIF([@[JOIN DATE]],$E$2,"M")<3,0,IF(DATEDIF([@[JOIN DATE]],$E$2,"Y")<1,([@[Current AL Entitlement ]]*(DATEDIF([@[JOIN DATE]],$E$2,"M"))/12),MIN(16,[@[Current AL Entitlement ]]+((DATEDIF([@[JOIN DATE]],$E$2,"M")-12)/12))))/0.5,0)*0.5
N15:N16,N10:N11	N10	=ROUND(IF(DATEDIF([@[JOIN DATE]],$E$2,"M")<3,0,IF(DATEDIF([@[JOIN DATE]],$E$2,"Y")<1,([@[Current AL Entitlement ]]*(DATEDIF([@[JOIN DATE]],$E$2,"M"))/12),MIN(18,[@[Current AL Entitlement ]]+((DATEDIF([@[JOIN DATE]],$E$2,"M")-12)/12))))/0.5,0)*0.5

Named Ranges
Name	Refers To	Cells
'LEAVE (2020)'!L_EMPSTDT	=T_EMP[JOIN DATE]	N6, G6:H6
L_EMPSTDT	=T_EMP[JOIN DATE]	N6, G6:H6

 

[Fluff]

Duplicate to: Leave entitlement pro rated

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this thread so please continue in the linked thread. If you do not receive a response, you can "bump" it by replying to it yourself, though we advise you to wait 24 hours before doing so, and not to bump a thread more than once a day.