Mariela wrote:
I have searched high and low and I cannot find an anwer to my dilemma and I
have tried a multitude of examples from various sources. My company accrues
PTO on bi-weekly basis as follows:

1 to 5 Years 4.62 Hours Biweekly
6 to 15 years 6.15 Hours Biweekly
15+ years 7.70

On one of the anniversary years, the employee changes the accrual of the
vacation on the paycheck of his anniversary. Example Employee Hire Date is
4/5/04, the employee will accrue 4.62 hours per paycheck until the paycheck
of 4/10/09 where he will start accruing at 6.15 for the remainder of the
paychecks for that year.

My Columns are
A1 Name
B1 Date of Hire
C1 Anniversary Date

I have tried HLookup to an array, I have tried IF formula's but nothing
gives me the right results. Is anyone out there able to help?????

Set up your accrual definitions somewhere like as follows (it is
important to include the zero row). Select this range and give it the
name ACCRUALS (Ctrl+F3 to define a named range).

col1 col2
0 0
1 4.62
6 6.15
15 7.70

This should return the correct accrual amount on the check date in column C:

=VLOOKUP(DATEDIF(B1,C1,"Y"),ACCRUALS,2,TRUE)