Vacation accrual formula needed

[jdcinama]

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.

 

[Sean Timmons]

A lot of this will depend on how your info looks now.

I'm thinking you'll want to create a list with yoru mnimum tenure for each
point and the number of accrued hours and rollovers.

0 8 180
2 9 244
5 10 268
etc

then, you can use
=if(month(A2)>=9,vlookup(2009-year(A2),2),vlookup(2008-year(A2),2))

to return the hours per month allowed and change the ,2 to ,3 to return the
max rollover in September.

Course, you can change the 2009 to year(B2) if B2 has current calculated
date (9/1/09 in this instance, I suppose).

From there, it's more about format and how you have things set up, but
hopign that gives you a head start.

 

[jdcinama]

Well, I've got 'Beginning Date' in B1, and 'TODAY()' in E2. Everyone's
anniversary date depends on the month they started work (ie: Start 2/6/07,
Anniversary 2/1 yearly). Employees get accrual on the 1st of each month. I've
got the list of years, accrual rate, rollover. B5 has a VLOOKUP to return the
rate of accrual based on that table. In E5, I have it calculating the
difference between B1 & E2 in months and multiplying by B5, then subtracting
any amounts entered in E6:E9999. Starting in E6 and going down, time taken
off can be entered.

The problem is what I do when accrual increase. If Mr. A has 24 months at an
accrual rate of 8 hours/month (192), and has taken 80 hours of vacation, that
leaves 112 hours of rollover. When his accrual rate changes from 8 to 9
hours/month, something has to be done with that 112, and start over with 9
hours added to the 112. The way I have it now, it just multiplies the TOTAL
months by the new accrual rate, and throws everything off. I'm still at a
loss. Thanks.