VLOOKUP results from Datedif (employee length of svcs related)

1st, change your table to be like this, instead...

	E	F
1	Years of Service	PTO Days Earned Per Year
2	0	10
3	1	25
4	3	30
5	50	40

Then use this formula...
=VLOOKUP(ROUNDUP((D2-C2)/365.25,4),Sheet2!$E$2:$F$5,2,1)+IF(E2>3,10,5)

Originally Posted by FDibbins

1st, change your table to be like this, instead...

	E	F
1	Years of Service	PTO Days Earned Per Year
2	0	10
3	1	25
4	3	30
5	50	40

Then use this formula...
=VLOOKUP(ROUNDUP((D2-C2)/365.25,4),Sheet2!$E$2:$F$5,2,1)+IF(E2>3,10,5)

Hi,

Thanks for your response. I want to use the 'Datedif' formula as the main indicator as to whether or not the employee completed the full year in relations to the respective anniversary date. Is there a way to revise the VLOOKUP with exact parameters?

As a work around, would it be easier if I just assigned the different Years of Service with an dentifier for the lookup (Basically, assign the every single Year of Service with the PTO. i.e. Year 0 = 10 PTO, Year 1 = 25 PTO, Year 2 = 25 PTO). etc.

The actual BONUS PTO tier is larger than the sample file. Employees gain the BONUS PTO when they reach the respective tiers. I apologize for the confusion.

The part of the formula I used to calc years of service, calcs the exact same thing as DATEDIF does (and perhaps more accurately)...
ROUNDUP((D2-C2)/365.25,4),
I am rounding up to 4 decimal places so dates like 9/30/2017 to 9/30/2018 would be worked out properly
(0.9993155)

Thanks for the rep