# Leave entitlement pro rated

#### ambz123

##### Board Regular
Hi All,

I am task to create a leave system to count the number of leave based on join date.
This is the formula im using
=IF(G9<3,0,IF(F9="M",IF(G9<24,16,IF(G9<=60,18,20)),IF(F9="ESO",IF(G9<24,14,IF(G9<=60,16,18)),IF(F9="TFO",IF(G9<24,10,IF(G9<=60,12,16)),IF(G9<24,8,IF(G9<=60,10,12))))))

Their entitlement is based on their position and length of services as shown in the attachment.
E.G Staff A position is Technician/Foreman/Office Staff = TFO, he work with us since 1 August 2019, his entitlement should be 10.5 .
In a Year we will giving them earn leave, so every 1 month he works he will earn 1 day leave and max is 10.5 for the entire year.
And when he work 2 years but less than 5 years he will be entitled to 16 days the next year.

Can i ask if there is any way to formula it to achieve such condition?

#### Attachments

• Capture.PNG
12.6 KB · Views: 21

### Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

#### Saba Sabaratnam

##### Board Regular
Hi

Set up a spreadsheet as given below .

And enter the formula in Cell B10

=IF(B9=0,"",INDEX(INDEX(\$C\$3:\$F\$5,,MATCH(B8,\$C\$2:\$F\$2,0)),MATCH(B9,\$B\$3:\$B\$5,1)))

Kind regards

Saba

#### Dave Patton

##### Well-known Member
- provide an explanation of your requirement with examples and expected results
- show how you calculate the numbers
- why is the number 10.5 in your example
- what solutions in previous posts on this subject worked for you

Consider the following; the named arrays just make the formula more concise.

T202101b.xlsm
FGH
9TFO1810
10TFO20
11TFO7216
5c
Cell Formulas
RangeFormula
H9H9=CHOOSE(MATCH(F9,{"ESO";"M";"TFO"},0),LOOKUP(G9,{0,0;3,14;24,16;60,18}),LOOKUP(G9,{0,0;3,16;24,18;60,20}),LOOKUP(G9,{0,0;3,10;24,12;60,16}))
H10:H11H10=CHOOSE(MATCH(F10,aA,0),LOOKUP(G10,aE),LOOKUP(G10,aM),LOOKUP(G10,aT))

Last edited:

#### ambz123

##### Board Regular
Employee A start work on 1 Aug 2019, he is a TFO AL entitlement start off with 10 days AL. However, firstly it should be pro-rated accordingly based on his joined date. Secondly, how i get 10.5 its because he has been with us for 18 months. So we pro-rated his leave in accordance to his number of months joined.

Condition as follow:

1. Entitlement based on Job Code
2. Entitlement based on join Date
3. Entitlement should be pro-rated based on joined date
4. Employee A works for 17 months which is more than 12 months he should be entitled to 10.5 days AL - (17-12)/12 + 10 days

Managerial = M
< 2 16
2=<5 18
5> 20
Executive/Supervisor/Officer = ESO
< 2 14
2=<5 16
5> 18
Technician/Foreman/Office Staff = TFO
< 2 10
2=<5 12
5> 16
Foreign Workers = FW
< 2 8
2=<5 10
5> 12

Excel Formula:
``=ROUND(IF(DATEDIF([@[JOIN DATE]],\$D\$2,"M")<3,0,IF(DATEDIF([@[JOIN DATE]],\$D\$2,"Y")<1,([@[AL Entitlement]]*(DATEDIF([@[JOIN DATE]],\$D\$2,"M"))/12),MIN(16,[@[AL Entitlement]]+((DATEDIF([@[JOIN DATE]],\$D\$2,"M")-12)/12))))/0.5,0)*0.5``

#### Attachments

• Capture.PNG
12.6 KB · Views: 6
• Capture1.PNG
25 KB · Views: 6

#### Dave Patton

##### Well-known Member

If you require or want suggestions, please provide a clear explanation of the leave entitlement, what you require and
several examples with expected results. How do you pro-rate etc

"10.5 its because he has been with us for 18 months"
but does you rate table not show 10?

Post an example of your sheet with the forum's XL2BB.

#### ambz123

##### Board Regular
If Employee Is Manager he is entitled to the following leave:

Managerial = M
less then 2 year = 16
equal to 2 year but less than 5 year = 18
6 and more = 20

Executive/Supervisor/Officer = ESO
less then 2 year = 14
equal to 2 year but less than 5 year = 16
6 and more = 18

Technician/Foreman/Office Staff = TFO
less then 2 year = 10
equal to 2 year but less than 5 year = 12
6 and more = 16

Foreign Workers = FW
less then 2 year = 8
equal to 2 year but less than 5 year = 10
6 and more = 12

I will use job code to find his leave entitlement, following by calculating his leave based on joined date.
Next i will use Actual month joined to tabulate the current accrued AL. However, this formula that i apply is more for year increment of leave entitlement it does not fit into my condition stated above.

Sheet1
ABCDEFGHIJ
Book1
11/25/2021
2EMPLOYEE NAMECITIZEN CODEJOIN DATEPOSITIONJOB CodeActual months joinedNo. of months joinedAL EntitlementCurrent Accrued ALYE Accrued AL
3Gan An HeanMAL12-Jul-19General ManagerM18291616.518
4Nislani Bin NaslinMAL1-Aug-19Mould FabricatorTFO17281010.512
RangeFormula
Cell Formulas
F3:F4F3=DATEDIF(C3,TODAY(),"m")
G3:G4G3=DATEDIF(C3,DATE(2021,12,31),"m")
I3:I4I3=ROUND(IF(DATEDIF(C3,\$D\$1,"M")<3,0,IF(DATEDIF(C3,\$D\$1,"Y")<1,(H3*(DATEDIF(C3,\$D\$1,"M"))/12),MIN(20,H3+((DATEDIF(C3,\$D\$1,"M")-12)/12))))/0.5,0)*0.5
J3:J4J3=IF(G3<3,0,IF(E3="M",IF(G3<24,16,IF(G3<=60,18,20)),IF(E3="ESO",IF(G3<24,14,IF(C3<=60,16,18)),IF(E3="TFO",IF(G3<24,10,IF(G3<=60,12,16)),IF(G3<24,8,IF(G3<=60,10,12))))))

#### Dave Patton

##### Well-known Member

Confirm the following:
The calculation shown below are correct and they yield the result(s) that you require,
If Employee worked exactly 24 months the accrual would be ____.
The calculations are all based on completed months.
There are no other factors that affect the calculations.

With the information that you provided, the calculations show a total of 15 days.
Which of the 3 alternative do you prefer?

T202101b.xlsm
ABCDEFGHIJ
1Leave Entitlement # of Days for Completed
2MonthsYearsMonthsTotal
31-Aug-1931-Dec-20TFO1710515
41-Aug-1931-Dec-20TFO1.4210515
51-Aug-1931-Dec-201715
5c
Cell Formulas
RangeFormula
H3H3=LOOKUP(INT(G3/12),\$L\$2:\$M\$4)
I3I3=MOD(G3,12)
H4H4=LOOKUP(INT(G4),\$L\$2:\$M\$4)
I4I4=INT((MOD(G4,1))*12)
G3,G5G3=DATEDIF(C3,D3+1,"m")
G4G4=YEARFRAC(C4,D4,3)
J3:J4J3=H3+I3
J5J5=LOOKUP(G5,O2:P23)

The Header is H1 should center across H1:J1

Last edited:

#### ambz123

##### Board Regular
Actually i just need something like this. I think the formula in column I just need some adjustment to get the correct figures.

MY - Leave Management.xlsm
ABCDEFGHI
11/27/202112/31/2021
2As ofAs ofAs of
3RequiredRequired1/27/202131/12/2131/12/21
4EMPLOYEE NAMEJOIN DATEPOSITIONJOB CodeActual months joinedNo. of months joinedYE AL EntitlementCurrent AL Entitlement Accrued AL
5Gan An Hean12-Jul-2019General ManagerM1829181617.5
6Nislani Bin Naslin01-Sep-2019Mould FabricatorTFO1827121011.5
11Mohd Azizi Bin Awang Su26-Sep-2019BatcherTFO1627121011.5
Sheet5
Cell Formulas
RangeFormula
B1B1=TODAY()
C1C1=DATE(YEAR(B1),12,31)
E3E3= TODAY()
E5E5=DATEDIF(B5,TODAY(),"m")
F5:F12F5=DATEDIF(B5,DATE(2021,12,31),"m")
G5:G12G5=IF(F5<3,0,IF(D5="M",IF(F5<24,16,IF(F5<=60,18,20)),IF(D5="ESO",IF(F5<24,14,IF(F5<=60,16,18)),IF(D5="TFO",IF(F5<24,10,IF(F5<=60,12,16)),IF(F5<24,8,IF(F5<=60,10,12))))))
H5:H12H5=IF(E5<3,0,IF(D5="M",IF(E5<24,16,IF(E5<=60,18,20)),IF(D5="ESO",IF(E5<24,14,IF(E5<=60,16,18)),IF(D5="TFO",IF(E5<24,10,IF(E5<=60,12,16)),IF(E5<24,8,IF(#REF!<=60,10,12))))))
I5I5=ROUND(IF(DATEDIF(B5,\$C\$1,"M")<3,0,IF(DATEDIF(B5,\$C\$1,"y")<1,(H5*(DATEDIF(B5,\$C\$1,"M"))/12),MIN(20,H5+((DATEDIF(B5,\$C\$1,"M")-12)/12))))/0.5,0)*0.5
E6:E12E6=DATEDIF(T_EMP[@[JOIN DATE]],TODAY(),"m")
I6:I12I6=ROUND(IF(DATEDIF(T_EMP[@[JOIN DATE]],\$C\$1,"M")<3,0,IF(DATEDIF(T_EMP[@[JOIN DATE]],\$C\$1,"y")<1,(H6*(DATEDIF(T_EMP[@[JOIN DATE]],\$C\$1,"M"))/12),MIN(20,H6+((DATEDIF(T_EMP[@[JOIN DATE]],\$C\$1,"M")-12)/12))))/0.5,0)*0.5

but my accrued AL figures is not correct. the correct number should be 17 for Gan An Hean, 10.5 for Nislani Bin Naslin.

#### ambz123

##### Board Regular
Actually i just need something like this. I think the formula in column I just need some adjustment to get the correct figures.

MY - Leave Management.xlsm
ABCDEFGHI
11/27/202112/31/2021
2As ofAs ofAs of
3RequiredRequired1/27/202131/12/2131/12/21
4EMPLOYEE NAMEJOIN DATEPOSITIONJOB CodeActual months joinedNo. of months joinedYE AL EntitlementCurrent AL Entitlement Accrued AL
5Gan An Hean12-Jul-2019General ManagerM1829181617.5
6Nislani Bin Naslin01-Sep-2019Mould FabricatorTFO1827121011.5
11Mohd Azizi Bin Awang Su26-Sep-2019BatcherTFO1627121011.5
Sheet5
Cell Formulas
RangeFormula
B1B1=TODAY()
C1C1=DATE(YEAR(B1),12,31)
E3E3= TODAY()
E5E5=DATEDIF(B5,TODAY(),"m")
F5:F12F5=DATEDIF(B5,DATE(2021,12,31),"m")
G5:G12G5=IF(F5<3,0,IF(D5="M",IF(F5<24,16,IF(F5<=60,18,20)),IF(D5="ESO",IF(F5<24,14,IF(F5<=60,16,18)),IF(D5="TFO",IF(F5<24,10,IF(F5<=60,12,16)),IF(F5<24,8,IF(F5<=60,10,12))))))
H5:H12H5=IF(E5<3,0,IF(D5="M",IF(E5<24,16,IF(E5<=60,18,20)),IF(D5="ESO",IF(E5<24,14,IF(E5<=60,16,18)),IF(D5="TFO",IF(E5<24,10,IF(E5<=60,12,16)),IF(E5<24,8,IF(#REF!<=60,10,12))))))
I5I5=ROUND(IF(DATEDIF(B5,\$C\$1,"M")<3,0,IF(DATEDIF(B5,\$C\$1,"y")<1,(H5*(DATEDIF(B5,\$C\$1,"M"))/12),MIN(20,H5+((DATEDIF(B5,\$C\$1,"M")-12)/12))))/0.5,0)*0.5
E6:E12E6=DATEDIF(T_EMP[@[JOIN DATE]],TODAY(),"m")
I6:I12I6=ROUND(IF(DATEDIF(T_EMP[@[JOIN DATE]],\$C\$1,"M")<3,0,IF(DATEDIF(T_EMP[@[JOIN DATE]],\$C\$1,"y")<1,(H6*(DATEDIF(T_EMP[@[JOIN DATE]],\$C\$1,"M"))/12),MIN(20,H6+((DATEDIF(T_EMP[@[JOIN DATE]],\$C\$1,"M")-12)/12))))/0.5,0)*0.5

but my accrued AL figures is not correct. the correct number should be 17 for Gan An Hean, 10.5 for Nislani Bin Naslin.
Hi how i get 10.5 is because from 1 sep 2019 to 1 sep 2020 = 10days cos he work 1 year is 10 days, under TFO job grade
From 1 sep 2020 onwards for year 2021 will be 10.5 days and from Sep to Dec is 3 months so we will pro-rate accordingly.

#### ambz123

##### Board Regular
Hi how i get 10.5 is because from 1 sep 2019 to 1 sep 2020 = 10days cos he work 1 year is 10 days, under TFO job grade
From 1 sep 2020 onwards for year 2021 will be 10.5 days and from Sep to Dec is 3 months so we will pro-rate accordingly.
So the formula is al entitlement + (months work based on 31/12/2021 - 12)/12

Replies
1
Views
133
Replies
13
Views
372
Replies
0
Views
136
Replies
23
Views
873
Replies
6
Views
650

### Forum statistics

1,130,211
Messages
5,640,877
Members
417,175
Latest member
Piccolo Transport

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back