Vacation Accrual Formula

[Halosty]

I have been tasked with developing a spreadsheet to track employee vacation accrual. The company pays employees fortnightly so I would like the spreadsheet to show the days that each employee has earned.

Our vacation policy is:
1-4 years = 10 days (80 hours/year, 1 Day/ 22 Days )
5 - 10 years = 15 days (120 hours/year, 1.5 Days/ 22 Days )
10 years = 20 days (160 hours/year, 2 Days/ 22 Days)

So far I have the following information set up:
A2 Name
B2 Hire Date
C2 TODAY ()
D2 =(C2-B2) (calculates the number of days since the employee was hired)
E3 I would like this to show the number of hours each employee has available to date.

I would greatly appreciate any help.

Thanks

 

[Dave Patton]

T202008b.xlsm
	A	B	C	D	E
1	Name	Hired	Today	Years	Accrual days
2		1-Jan-17	13-Aug-20	3	10
3		1-Aug-10	13-Aug-20	10	20
3a

Cell Formulas
Range	Formula
C2:C3	C2	=TODAY()
D2:D3	D2	=DATEDIF(B2,C2,"y")
E2:E3	E2	=LOOKUP(D2,{0,10;5,15;10,20})

 

[Halosty]

T202008b.xlsm
	A	B	C	D	E
1	Name	Hired	Today	Years	Accrual days
2		1-Jan-17	13-Aug-20	3	10
3		1-Aug-10	13-Aug-20	10	20
3a

Cell Formulas
Range	Formula
C2:C3	C2	=TODAY()
D2:D3	D2	=DATEDIF(B2,C2,"y")
E2:E3	E2	=LOOKUP(D2,{0,10;5,15;10,20})

Updating D2 =NETWORKDAYS.INTL(C2,D2,1) ( This is the Network Days)
E2 = I would like this to show the accrual amount up to the balance up to the current date the employee has worked. There is a accrual cap each year.

Updated everything. Hope this clarifies it more. Thanks for your help. Your formula didn't take into consideration that they only accrue a day for each 22 day worked then its capped until the work year as passed. Then it rolls over to the new year

 

[Dave Patton]

Provide a clear explanation of what you require.
Post a few examples with the expected results.

 

[Halosty]

I need the accrued vacation rate to correctly multiple the net days worked to show Vacation Accrual.

Example.

Hire Date: 28/1/2013
Years of Service: 2 (So the Accrual Rate would be 1/22) (They Gain 1 day for every 22 days worked)
Separation Date: 28/2/2014

This would come to 285 Net Days Worked. Using the Accrual Formula it would be (220 * (1/22)) + ((285-262)+(1/22))

262 Is the Yearly Network days. So they only start to accrue vacation balance after a year of net work days so accrual gained in capped per year.

The formulas I have don't contain the functions to cap the accrual for each year so I am hoping someone can help.

1-4 Years: =IF((AND(E2<1310)),E2*$M$2,IF((OR(F2>=1&F2<5)),50,50))
5-9 Years: =IF((AND(E2>=1100,E2<2200)),(E2-1100)*$N$2,IF((OR(F2>5)),75,0))
10 Years: =IF((OR(E2>=2200)),(E2-2200)*$O$2,0)

This is what I have

Vacation Balance.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	NAMES	Hire Date	Separation Date	Days	Years	Eligible	Entitlement Days	1-4 Years	5-9 Years	10 Years	Accrual	1 -4 years	5- 9 Years	10 Years2
2	Employee 1	28/1/2013	28/2/2014	285	1	Yes	10	12.95	0.00	0.00	0.05	0.045454545	0.068181818	0.090909091
3	Employee 2	28/1/2013	14/8/2020	1970	7	Yes	15	50.00	59.32	0.00	0.07	0.05	0.07	0.09
Sheet1

Cell Formulas
Range	Formula
D2:D3	D2	=NETWORKDAYS.INTL(B2,C2,1)
E2:E3	E2	=DATEDIF(B2,C2,"Y")
F2:F3	F2	=IF(D2>=110,"Yes","No")
G2:G3	G2	=10+LOOKUP(E2,{0,1,2,3,4,5,6,7,8,9,10},{-10,0,0,0,0,5,5,5,5,5,10,10})
H2:H3	H2	=IF((AND(D2<1310)),D2*$L$2,IF((OR(E2>=1&E2<5)),50,50))
I2:I3	I2	=IF((AND(D2>=1100,D2<2200)),(D2-1100)*$M$2,IF((OR(E2>5)),75,0))
J2:J3	J2	=IF((OR(D2>=2200)),(D2-2200)*$N$2,0)
K2:K3	K2	=IF((D2)/262<5,(1/22),IF((D2)/262<10,(1.5/22),IF((D2)/262>=10,(2/22),(2/22))))
L2:L3	L2	=10/220
M2:M3	M2	=15/220
N2:N3	N2	=20/220
C3	C3	=TODAY()

I hope I explained it well enough.

Thanks again.

 

[Dave Patton]

The commentary on how the Vacation Accrual is calculated was not complete.
- If part days are not accrued , use Int and/or rounding as required.
- helper columns,Vlookup for the accumulated amounts, etc. could make the formula more concise
- You can name the lookup information; see I5

T202008b.xlsm
	A	B	C	D	E	F			I
1	NAMES	Hire Date	Annual Date	Separation Date	Part Year/22	Years			Vacation Accrual
2	Employee 1	28-Jan-13	28-Jan-14	28-Feb-14	1.09	1			11.09
3	Employee 2	28-Jan-13	28-Jan-20	16-Aug-20	6.55	7			94.82
4	Employee 3	1-Jan-00	1-Jan-20	16-Aug-20	7.41	20			344.82
5	Employee 4	1-Jan-00	1-Jan-20	12-Nov-20	10.32	20			350.00
6
3aa

Cell Formulas
Range	Formula
E2:E5	E2	=NETWORKDAYS(C2,D2,1)/22
F2:F5	F2	=DATEDIF(B2,D2,"Y")
I2:I4	I2	=(F2>0)*(MIN(F2,4)*10)+(F2>4)*((MIN(F2,10)-4)*15)+(F2>10)*(F2-10)*20+MIN(E2*VLOOKUP(F2,{0,1,10;5,1.5,15;11,2,20},2),VLOOKUP(F2,{0,1,10;5,1.5,15;11,2,20},3))
I5	I5	=(F5>0)*(MIN(F5,4)*10)+(F5>4)*((MIN(F5,10)-4)*15)+(F5>10)*(F5-10)*20+MIN(E5*VLOOKUP(F5,aRngL,2),VLOOKUP(F5,aRngL,3))

 

[Dave Patton]

T202008b.xlsm
	A	B	C	D	E	F	G		I
1	NAMES	Hire Date	Annual Date	Separation Date	Part Year/22	Years			Vacation Accrual
2	Employee 1	28-Jan-13	28-Jan-14	28-Feb-14	1.09	1			11.09
3	Employee 2	28-Jan-13	28-Jan-20	16-Aug-20	6.55	7			94.82
4	Employee 3	1-Jan-00	1-Jan-20	16-Aug-20	7.41	20			344.82
5	Employee 4	1-Jan-00	1-Jan-20	12-Nov-20	10.32	20			350.00
3aa

Cell Formulas
Range	Formula
E2:E5	E2	=NETWORKDAYS(C2,D2,1)/22
F2:F5	F2	=DATEDIF(B2,D2,"Y")
I2:I5	I2	=SUMPRODUCT(--(F2>rB),F2-rB,rD)+MIN(E2*VLOOKUP(F2,aRngL,2),VLOOKUP(F2,aRngL,3))

Named Ranges
Name	Refers To	Cells
rB	='3aa'!$J$2:$J$4	I2:I5
rD	='3aa'!$L$2:$L$4	I2:I5