Leave entitlement

ambz123

Board Regular
Joined
Aug 27, 2020
Messages
53
Office Version
  1. 2019
Platform
  1. Windows
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
 
Hi Alan i tried the formula to removed the Int but the answer is still not correct.
For the example below her leave suppose to be 7.5 instead of 8.2. How i get 7.5 is i take no. completed months/ 12 * 7
1602662127265.png
 
Upvote 0

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
I've amended the formula's pro-rata based on months

Book1
ABCD
1Join dateStatusAnnual leave @14/10/2020
216/08/2019NONSUP77.6
301/01/1900NONSUP714
412/08/2014SUP1014
513/07/2018NONSUP79.3
630/04/2011NONSUP714
707/07/2020NONSUP71.8
824/11/2017SUP1012.8
905/11/2015NONSUP711.9
1019/05/2013SUP1014
1118/02/2013NONSUP714
Sheet1
Cell Formulas
RangeFormula
D1D1=TODAY()
D2:D11D2=ROUND(IF(DATEDIF(A2,$D$1,"M")<3,0,IF(DATEDIF(A2,$D$1,"Y")<2,(C2*(DATEDIF(A2,$D$1,"M"))/12),MIN(14,C2+((DATEDIF(A2,$D$1,"M"))/12)))),1)
 
Upvote 0
and this round to half day

Book1
ABCD
1Join dateStatusAnnual leave @14/10/2020
216/08/2019NONSUP77.5
301/01/1900NONSUP714
412/08/2014SUP1014
513/07/2018NONSUP79.5
630/04/2011NONSUP714
707/07/2020NONSUP72
824/11/2017SUP1013
905/11/2015NONSUP712
1019/05/2013SUP1014
1118/02/2013NONSUP714
Sheet1
Cell Formulas
RangeFormula
D1D1=TODAY()
D2:D11D2=ROUND(IF(DATEDIF(A2,$D$1,"M")<3,0,IF(DATEDIF(A2,$D$1,"Y")<2,(C2*(DATEDIF(A2,$D$1,"M"))/12),MIN(14,C2+((DATEDIF(A2,$D$1,"M"))/12))))/0.5,0)*0.5
 
Upvote 0
and this round to half day
[....]
=ROUND(IF(DATEDIF(A2,$D$1,"M")<3,0,IF(DATEDIF(A2,$D$1,"Y")<2,(C2*(DATEDIF(A2,$D$1,"M"))/12),MIN(14,C2+((DATEDIF(A2,$D$1,"M"))/12))))/0.5,0)*0.5


Without vetting the DATEDIF calculations, a simpler form is:

=MROUND(IF(DATEDIF(A2,$D$1,"M")<3, 0, IF(DATEDIF(A2,$D$1,"Y")<2, C2*DATEDIF(A2,$D$1,"M")/12, MIN(14, C2+DATEDIF(A2,$D$1,"M")/12))), 0.5)

Note: I usually deprecate the use of MROUND with a decimal fraction in the second parameter, due to binary arithmetic anomalies. But this should work find because the second parameter
 
Upvote 0
Hi Both,

thanks a lot for the suggestion. But i think for this guy the formula doesnt fetch the correct computation.
1602729300985.png

He has work for 23 months which is nearly 2years but his leave entitlement shows 13.5 but it should 7.5 instead.
 
Last edited:
Upvote 0
Col D rounded to half day of Col E

with joeu2004's suggestion of the MROUND()


Book1
ABCDE
1Join dateStatusAnnual leave @15/10/2020
216/08/2019NONSUP777.083333
302/11/2018NONSUP787.916667
412/08/2014SUP101414
513/07/2018NONSUP78.58.25
630/04/2011NONSUP71414
707/07/2020NONSUP721.75
824/11/2017SUP101211.83333
905/11/2015NONSUP71110.91667
1019/05/2013SUP101414
1118/02/2013NONSUP713.513.58333
Sheet1
Cell Formulas
RangeFormula
D1D1=TODAY()
D2:D11D2=MROUND(IF(DATEDIF(A2,$D$1,"M")<3,0,IF(DATEDIF(A2,$D$1,"Y")<1,(C2*(DATEDIF(A2,$D$1,"M"))/12),MIN(14,C2+((DATEDIF(A2,$D$1,"M")-12)/12)))),0.5)
E2:E11E2=IF(DATEDIF(A2,$D$1,"M")<3,0,IF(DATEDIF(A2,$D$1,"Y")<1,(C2*(DATEDIF(A2,$D$1,"M"))/12),MIN(14,C2+((DATEDIF(A2,$D$1,"M")-12)/12))))
 
Last edited:
Upvote 0
Hi Alan, i tried your formula, i got the right answer for the guy that join in 2/11/2018 however, i still get an incorrect answer for the one whom join in at 16/08/2019. She should be getting 7.5days of leave
1602749952874.png
 
Upvote 0
for 7.5 days of leave she needed to be joined 18 months ago (1/2 day for the extra 6 months).
the leave calculation should be 7 + (13-12)/12 = 7.1 days, isn't that right?
 
Upvote 0
Hi Alan, for her case we take 16 Aug 2019 to 16 Aug 2020 = 7 days
so 16 Aug 2020 onwards
the YTD for Year 2020 will be 7.5 days and from Aug till Dec is about 4 mths so we will pro-rate accordingly
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,185
Members
448,554
Latest member
Gleisner2

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