Vacation accrual based on years with rollover

[jdcinama]

I'm trying to work out a formula that will calculate vacation accrual with
rollover. Where I work, the accrual rate & rollover is as follows:
0-1 year: 8 hours/month, max 180 rollover
2-4 years: 9 hours/month, max 244 rollover
5-9 years: 10 hours/month, max 268 rollover
10-14 years: 11 hours/month, max 292 rollover
15-19 years: 13 hours/month, max 340 rollover
20-24 years: 15 hours/month, max 388 rollover
25-29 years: 17 hours/month, max 436 rollover
30-34 years: 19 hours/month, max 484 rollover
35+ years: 21 hours/month, max 532 rollover

Vacation hours rollover to the next year if not used, below the rollover
cutoff points. Anything beyond the cutoff point becomes sick time at the
beginning of each fiscal year (9/1). Sick time accrual is no problem, as it
remains 8 hours/month throughout employment. I've got a sheet worked out that
shows current amount of leave with a place to enter hours as they are used,
and it calculates everything wonderfully thus far. My problem is trying to
figure out how to take into account when accrual rates change. It starts
calculating everything by the new accrual rate.

Example: Mr. A has 100 hours of vacation at the time he approaches an
accrual rate change. How do I get it to keep that 100, and calculate at the
new rate from that day forward? And, how do I do that for each progressive
change?

I just can't wrap my head around the logic/syntax of how to make this work.
Any help on this is GREATLY appreciated. Thanks.

 

[cm]

Since you already have the base worksheet done, I would try modifying it as
follows. I would create 9 separate accrual columns for how much the person
would accrue for each period (0-1st year, 2nd-4th year etc.) and then add
them together to get the total accrual. The column formulas should be zero if
they have not passed that anniversary date yet. You can hide the 9 columns
and just display the 10th with the total.

IF ther person has been there 5 years, you know the total for the period
0-1st year would be 8X12, the second period total would be 9x12 , and the 3rd
period total would be prorated by month, as that would be the current
accrual. The rest of the 9 columns would be zero, as the person has not
reached that seniority yet.