Vacation Time Formula

[Stixbaraca]

First off, I apologize if this question has already been asked and answered! I am looking for a formula to determine employee vacation time. Our VT is distributed in hours not days, and no hours can be carried over to the next calendar year. My spreadsheet already has employment start dates at B2 through B20 (small company!) My vacation time values will be listed in F2 through F20. Our annual vacation is determined by years of service. All employee's vacation hours for the current year are earned as of the employee's anniversary date. Below is the amount of hours per year.

1 year through 5 years of service - 80 Hours vacation
6 years through 11 years of service - 120 Hours vacation
12 years through 16 years of service - 180 Hours vacation
17 years through 20 years of service - 200 Hours Vacation
21 years of service or more - 240 Hours vacation

Thanks a million for all the help!

 

[DRSteele]

Welcome to the forum.

I took the time to create some sample data. Copy the formulas downwards.

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]

Book1
	B	C	D	E	F	G	H	I
1	10/15/2019
2
3	Workman Start Date	Days of Service	Years of Service		Vacation Hours
4	1/1/1997	8322	22.8		240
5	4/9/2001	6763	18.5		200
6	12/13/2007	4324	11.8		120
7	1/28/2008	4278	11.7		120
8	8/19/2009	3709	10.2		120
9	12/29/2018	290	0.8		0		Years of Service	Vacation Hours
10	4/30/2003	6012	16.5		180		0	0
11	3/14/2016	1310	3.6		80		1	80
12	4/10/2015	1649	4.5		80		6	120
13	8/28/1997	8083	22.1		240		12	180
14	4/3/2017	925	2.5		80		17	200
15	6/28/2009	3761	10.3		120		21	240
16	3/15/2019	214	0.6		0
17	6/25/2000	7051	19.3		200
18	8/19/1998	7727	21.2		240
19	8/1/2003	5919	16.2		180
20	11/14/2010	3257	8.9		120
21	5/13/2004	5633	15.4		180
22	8/31/2006	4793	13.1		180
23	12/17/2007	4320	11.8		120
Sheet44

Cell Formulas
Range	Formula
C4	=$B$1-B4
D4	=C4/365
F4	=VLOOKUP(D4,$H$10:$I$15,2)

[/FONT]