Leave entitlement pro rated

A

ambz123

Board Regular
Joined
Aug 27, 2020
Messages
53
Office Version
  1. 2019
Platform
  1. Windows
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
    Capture.PNG
    12.6 KB · Views: 57

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Sort by date Sort by votes
Hi

Set up a spreadsheet as given below .

1611564366541.png


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
 
Upvote 0
Please post an extract of your sheet with the forum's XL2BB.
Is your formula working correctly?
- 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:
Upvote 0
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
    Capture.PNG
    12.6 KB · Views: 38
  • Capture1.PNG
    Capture1.PNG
    25 KB · Views: 40
Upvote 0
Thanks for the reply.
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.
 
Upvote 0
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))))))
 
Upvote 0
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:
Upvote 0
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
7Mohammad Zamir Bin Abdul Jalal02-Sep-2019TechnicianTFO1627121011.5
8Muhammad Syafiq Ezzuddin Bin Zulkefeli26-Sep-2019TechnicianTFO1627121011.5
9Muhamad Hafis Bin Jumhat10-Sep-2019EngineerESO1627161415.5
10Mohd Fazwan Bin Shahad18-Sep-2019SupervisorESO1627161415.5
11Mohd Azizi Bin Awang Su26-Sep-2019BatcherTFO1627121011.5
12Muhammad Azeem Bin Nordin07-Oct-2019TechnicianTFO1626121011.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.
 
Upvote 0
ambz123 said:
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
7Mohammad Zamir Bin Abdul Jalal02-Sep-2019TechnicianTFO1627121011.5
8Muhammad Syafiq Ezzuddin Bin Zulkefeli26-Sep-2019TechnicianTFO1627121011.5
9Muhamad Hafis Bin Jumhat10-Sep-2019EngineerESO1627161415.5
10Mohd Fazwan Bin Shahad18-Sep-2019SupervisorESO1627161415.5
11Mohd Azizi Bin Awang Su26-Sep-2019BatcherTFO1627121011.5
12Muhammad Azeem Bin Nordin07-Oct-2019TechnicianTFO1626121011.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.
Click to expand...
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.
 
Upvote 0
ambz123 said:
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.
Click to expand...
So the formula is al entitlement + (months work based on 31/12/2021 - 12)/12
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Locked
  • Question Question
Leave Entitlement to count pro-rated leave based on joined date.
Replies
1
Views
2K
Fluff
Fluff
A
  • Question Question
Pro-rated leave for range
2
Replies
13
Views
2K
ambz123
A
A
  • Question Question
Pro Rated Annual leave with different conditions
Replies
0
Views
539
ambz123
A
A
  • Question Question
Leave entitlement
2 3
Replies
23
Views
2K
ambz123
A
A
  • Question Question
Pro-Rated Leave Entitlement
Replies
6
Views
5K
ambz123
A

Forum statistics

Threads
1,224,826
Messages
6,181,192
Members
453,021
Latest member
pingpong7117

Share this page

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top