How to calculate vacation accrual based on 5 days every 5 years

[demtro]

I just watched the Youtube Excel Magic Trick #202 for calculating vacation days. This is a great starting point for me. However my company starts you out with 10 days, after 5 years you get 5 more days, and after 10 years you get 5 more days, 15 5 more days for a cap of 25 days. I need some help modifying the formula to calculate vacation based on year of hire, current, year, and accumulation of those additional 5 days for each 5 year increment up to a cap of 25....I tried playing with the formula, but cant figure it out. Not a math wiz, so any help is appreciated.

thanks
Demtro

 

[Dave Patton]

Book1
	A	B	C	D	E
1		Date of Hire	Current Date	Years
2		01-Jan-00	06-Jan-20	20	25
3		01-Jan-00	06-Jan-20		25
4					25
5
6			0	10
7			5.01	15
8			10.01	20
9			15.01	25
1e

Cell Formulas
Range	Formula
D2	D2	=DATEDIF(B2,C2,"y")
E2	E2	=(D2>0)*10+(D2>5)*5+(D2>10)*5+(D2>15)*5
E3	E3	=LOOKUP(YEARFRAC(B3,C3),C6:D9)
E4	E4	=LOOKUP(YEARFRAC(B3,C3),{0,10;5.01,15;10.01,20;15.01,25})

A few alternatives that you can try.
Please check the formula with your actual data to ensure that it yields what you require.
E4 is the same formula as E3 but the lookup information is built in.

 

[Dave Patton]

Try the alternative that you prefer.

Book1
	B	C	D	E	F	G
1	Date of Hire	Current Date
2	01-Jan-00	06-Jan-20	20.03	25	25	25
3	01-Jan-16	06-Jan-20			10	10
1e

Cell Formulas
Range	Formula
D2	D2	=(C2-B2)/365
E2	E2	=(D2>0)*10+(D2>5)*5+(D2>10)*5+(D2>15)*5
F2	F2	=LOOKUP(D2,$C$7:$D$10)
G2	G2	=MIN(CEILING(YEARFRAC(B2,C2),5)+5,25)
F3	F3	=LOOKUP((C3-B3)/365,{0,10;5.01,15;10.01,20;15.01,25})
G3	G3	=MIN(CEILING((C3-B3)/365,5)+5,25)

 

[mikerickson]

IF A1 has years of service =MIN(25, 10+FLOOR(A1,5)) will give you the number of vacation days accrued for that year.