Employee Vacation Accrual Formulas

[CasandraBeaulieu]

I have been tasked with developing a spreadsheet to track employee vacation accrual. The Company updates the vacation accrual the last day of every month.

Our hourly vacation policy is:
0-2 years = 6 days
3-5 years = 10 days
6 years = 15 days

Our salary vacation policy is:
0-5 Years =15 days
6 Years = 20 days

So far I have the following information set up:
A2 Name
B2 Hire Date
C2 TODAY ()
D2 =(C2-B2)/7 (calculates the number of weeks since the employee was hired)
E2 I'd like to reflect the vacation time the employee has taken.
F2 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]

T202008a.xlsm
	A	B	C	D	E	F
1	Name	Hire	Calc Date	Years	Type	Earned
2	A	1-Jan-10	6-Aug-20	10	S	20
3	B	1-Jan-19	6-Aug-20	1	Hr	6
4
4b

Cell Formulas
Range	Formula
D2:D3	D2	=DATEDIF(B2,C2,"y")
F2:F3	F2	=IF(E2="Hr",LOOKUP(D2,{0,6;3,10;6,15}),IF(E2="S",LOOKUP(D2,{0,15;6,20}),""))