Semi-Complex Vacation Accrual Spreadsheet Template Help

Hi All,

I'm looking to make an excel spreadsheet that can track a fairly complex vacation accrual policy.

The policy is outlined as follows:

(based on hire date anniversary)
0-1 year of service - 0 vacation hours (0.0 accrual rate)
1-2 years service - 40 vacation hours (1.54 accrual rate)
>2 years service - 80 vacation hours (3.08 accrual rate)

In addition, there is no carry-over (the accrued time resets on Jan. 1 of each year).

So for example, if Employee A has a hire date of 2/23/2013, on 2/23/2014 they would begin to accrue vacation at 1.54/hours per pay period (which will reset on Jan. 1) - keeping the same accrual rate of 1.54/hours per pay period until 2/23/15 when they will begin to accrue at 3.08 hours/pay period.

I've gotten as far as being able to determine what accrual rate each employee is at currently (based on today's date vs hire date) but am having trouble accounting for the earned time pre/post their anniversary dates mid-year (see attached).

Any help would be greatly appreciated!

Thanks in advance,

Evan