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
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
try this

Book1
ABCD
1Join dateStatusAnnual leave @09/09/2020
213/07/2014NONSUP713.0
314/11/2019SUP108.2
412/08/2014SUP1014.0
513/07/2018NONSUP79.0
630/04/2011NONSUP714.0
701/12/2010NONSUP714.0
824/11/2017SUP1012.0
905/11/2015NONSUP711.0
1019/05/2013SUP1014.0
1118/02/2013NONSUP714.0
Sheet1
Cell Formulas
RangeFormula
D1D1=TODAY()
D2:D11D2=IF(($D$1-A2)<=365,C2*($D$1-A2)/365,MIN(14,C2+INT(($D$1-A2)/365)))
 
Upvote 0
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.


=IF(($D$2-D6)<=365,J6*($D$2-D6)/365,MIN(14,J6+INT(($D$2-D6)/365)))
 
Upvote 0
that should do it

Book1
ABCD
1Join dateStatusAnnual leave @12-Sep-2020
213-Jul-2014NONSUP713.0
310-Sep-2019NONSUP77.0
412-Aug-2014SUP1014.0
513-Jul-2018NONSUP79.0
630-Apr-2011NONSUP714.0
701-Dec-2010NONSUP714.0
824-Nov-2017SUP1012.0
905-Nov-2015NONSUP711.0
1019-May-2013SUP1014.0
1118-Feb-2013NONSUP714.0
Sheet1
Cell Formulas
RangeFormula
D1D1=TODAY()
D2:D11D2=IF(($D$1-A2)<=2*365,INT(C2*($D$1-A2)/365),MIN(14,C2+INT(($D$1-A2)/365)))
 
Upvote 0
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

1601969935921.png
 
Upvote 0
try this

Book1
ABCD
1Join dateStatusAnnual leave @06/10/2020
201/07/2020NONSUP71
301/01/1900NONSUP714
412/08/2014SUP1014
513/07/2018NONSUP79
630/04/2011NONSUP714
707/07/2020NONSUP70
824/11/2017SUP1012
905/11/2015NONSUP711
1019/05/2013SUP1014
1118/02/2013NONSUP714
Sheet1
Cell Formulas
RangeFormula
D1D1=TODAY()
D2:D11D2=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))))
 
Upvote 0
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?
 
Upvote 0
you can take out the int() like this

Book1
ABCD
1Join dateStatusAnnual leave @14/10/2020
216/08/2019NONSUP78.2
301/01/1900NONSUP714.0
412/08/2014SUP1014.0
513/07/2018NONSUP79.3
630/04/2011NONSUP714.0
707/07/2020NONSUP71.9
824/11/2017SUP1012.9
905/11/2015NONSUP711.9
1019/05/2013SUP1014.0
1118/02/2013NONSUP714.0
Sheet1
Cell Formulas
RangeFormula
D1D1=TODAY()
D2:D11D2=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))))
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,943
Members
448,534
Latest member
benefuexx

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