Formula to cap vacation hour accrual

[trietta]

Trying to figure out how to cap the amount of hours someone accrues for vacation based on company policy.
Let's say that an employee caps out at 37 days and earns 25 days per year at a rate of 2.1 days per week.
My formula that calculate the number of hours available is: =(MONTH(H$1)*G5)+(B2*8); H$1 is today's date, G5 is the weekly accrual rate in hours, and B2 is the number of days that the person is carrying over from last year.

Thoughts??

[ben_hensel]

=MIN( calc, 37)

Use the minimum function so that if the calc goes above 37 days, it just returns 37 instead.

[Zer0Cool]

A sample would be very helpful.

Despite your description there is still some information missing/unclear.

For example, you state the accrual rate is weekly but your formula appears to apply it by month, not week.

I think the general answer here however is to wrap your formula into an IF or some other logic:

psuedo formula:

Please Login or Register  to view this content.

Also, does vacation time ever expire? If so that greatly complicates matters.

[trietta]

Thanks. Vacation doesn't expire, but when you reach your max, you can't accrue any more time until you take vacation time.

[trietta]

If I use MIN function, will the formula revert back to the regular calc once he's taken a bunch of days off to get under the cap?

[ben_hensel]

If I use MIN function, will the formula revert back to the regular calc once he's taken a bunch of days off to get under the cap?

Yes.

MIN(calc, 37) will do the calc, compare it to 37, and then return whichever is less.

So if you update the information feeding it such that the calc returns, say, 35, then the MIN will return 35.