I posted this in 'Worksheet Functions', but haven't gotten a nibble. I'm
reposting here. Thanks.

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.