Pro-Rated Leave Entitlement

[ambz123]

Hi All
Need your help to with calculating the pro-rated(current leave balance) assuming that the employee start on 9 Mar 2020 and ends on 31 Dec 2020. So the result will be as follow:

IF is SUP entitled 10 days + 1 day every subsequent year till max 14,
Join on 9 Mar 2020 to 31 Dec 2020 = 175 days
175/365 = 0.47
10 days entitled x 0.47 = 4.7 pro rated leave

IF NON SUP entitled 7 days + 1 day every subsequent year till max 14;
Join on 10 Sept 2019 to 31 Dec 2020 = 356 days
356/365 = 0.97
7 days entitled x 0.47 = 6.7 leave

Another condition is assuming that the employee has served the company for 1.5 years and has accumulated leave of 7 days from his 1st year of service and on his 2nd year he should be entitled 8 days leave, so the result so be 7+(6*8/12)= 10.9 days.

 

[Dave Patton]

Could you show how you calculated the numbers in your example?

T202008c.xlsm
	D	E	F	G	H	I	J	K
7	9-Mar-20	31-Dec-20	298			SUP		8.11
8	10-Sep-19	31-Dec-20	479			NONSUP		9.16
6a

Cell Formulas
Range	Formula
F7:F8	F7	=E7-D7+1
K7:K8	K7	=MIN(14,YEARFRAC(D7,E7)*IF(I7="SUP",10,7))

 

[ambz123]

The calculation is as follow:
Example A
IF is SUP entitled 10 days + 1 day every subsequent year till max 14,
Join on 9 Mar 2020 to 31 Dec 2020 = 175 no of days work
175/365 = 0.47
10 x 0.47 = 4.7
Example B
IF NON SUP entitled 7 days + 1 day every subsequent year till max 14;
Join on 10 Sept 2019 to 31 Dec 2020 = 356 no of days work
356/365 = 0.97
7 x 0.47 = 6.7

So in my example A i should get a prorated no. of leave of 4.7 days while in my example B i should get a prorated no. of leave of 6.7 days

I tired using the following method but its not tabulating the correct no. of leaves

It fetch me the condition for IF SUP 10 If False 7. But not calculating the prorated leave based on their join date. Please help me

 

[Dave Patton]

You did not show how you calculate the numbers and you now use term " no of days work"
Do you intend to allocate by working days in a year? how do you calculate working days per year?
What are your work days and how did you calculate the number of work days?
I completed a quick example. Please check the results and provide complete information so that we
can provide a relevant solution. If you provide the business definition of how the numbers are to be calculated and your calculations for the sample provided,
the formulas can be edited/expanded as necessary.

T202008c.xlsm
	D	E	F	G	H	I	J	K
1	Start	End	Annual	Years		Annual	Accrual	Total
2	9-Mar-20	31-Dec-20			SUP		8.17	8.17
3	10-Sep-19	31-Dec-20	10-Sep-20	1	NONSUP	7	2.47	9.47
4	1-Jan-10	10-Sep-20	31-Dec-19	10	SUP	130	9.78	139.78
6a

Cell Formulas
Range	Formula
J2	J2	=NETWORKDAYS(D2,E2)/262*10
K2:K4	K2	=I2+J2
J3	J3	=NETWORKDAYS(F3,E3)/262*(7+1)
I4	I4	=VLOOKUP(G4,M2:O11,3)
J4	J4	=NETWORKDAYS(F4,E4)/262*14

 

[ambz123]

for no. of days work i use the following method:

=DATEDIF([@[JOIN DATE]],TODAY(),"YD") and i get 358 days work
from there i will use the YD to count the pro-rated leave they entitled.

 

[Dave Patton]

T202008c.xlsm
	D	E	F	G	H	I	J	K	L
1	Start	End	Annual	Years	Vacation accrual	Annual	Accrual	Total	2-Sep-20
2	10-Sep-19			0	NONSUP		6.88
3	10-Sep-19			0	NONSUP		6.87
6a

Cell Formulas
Range	Formula
G2	G2	=DATEDIF(D2-1,L1,"y")
G3	G3	=DATEDIF(D3-1,L1,"y")
J2	J2	=(L1-D2+1)/365*IF(H2<>"Sup",VLOOKUP(G2,$M$13:$O$25,2),VLOOKUP(G2,$M$1:$O$11,2))
J3	J3	=YEARFRAC(D3,L1,3)*7

To ambz123
You have not clearly explained your question and you have not answered questions.
I cannot provide a solution without better information.
Did you review the previous suggestions?

 

[ambz123]

I not too sure how to explain in business term. But the condition of my requirement is;

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.