If statement

[Phyllis]

Okay, I have a complicated if statement that I need to
complicate a little further. Here's the criteria:

An employee is eligible for 120 hours of vacation per
year, accrued from their date of hire. However, the
employee can only accrue a maximum of two times their
eligible vacation hours. So when they reach 240 hours
they stop accruing until they use some of their time.
Once their total drops below 240 then it will start to
accrue again. The accrual rate is 2.3076 hours per week.
Here's my formula, is their any way that I can get it to
stop and start accruing again based on how many available
hours they have?

A1 = date of hire
A2 = Anniversary Date
A3 = hours carried over from prior year
A4 = Eligible hours
A5 = Accrual Date
A6 = =if(A4=120,($A$5-A1)/7*2.3076,0)
A7 = =if(and(A4=120,A6+A3-A8>240),240,A6+A3)
A8 = Used vacation hours

My formula in A7 works except that when the hours drop
below 240, it adds the amount in A6. However, the amount
in A6 is the actual hours that would have accrued from the
anniversay date to the accrual date, which is incorrect.
The accrued hours should stop as long as the total is at
240, then start again when it drops below. The lost
accrual hours should not be recovered once the accrued
hours drops below the 240.

I hope this make sense. Any help would be very much
appreciated. Thanks.

 

[Michael J. Malinsky]

I'm not clear as to what the formulae in A6 and A7 are supposed to
represent.

--
Michael J. Malinsky
Pittsburgh, PA

"I was gratified to be able to answer promptly,
and I did. I said I didn't know." -- Mark Twain

 

[Phyllis]

Hi Mike Malinksy,

Thanks for looking at my question. I'm sorry, the results
of the formula in A6 are the hours accrued from the
anniversary date to the accrual date. The results of A7
should be the sum of the hours carried over from prior
year (A3) and the accrued hours in A6. What I need is for
the formula in A7 to stop accruing if the sum of A3 (carry
over) plus A6 (accrued) minus A8 (hours used)is => 240,
then start accruing again if the sum of those cells is <
240.

For example: An employee has an anniversary date of
1/1/04 and on 3/1/04 they reached their 240 hour cap of
accrued vacation. They should not accrue any additional
hours until they used some and dropped below 240. Then
say they used 40 hours the w/e ending 3/12/04, and their
accrued hours dropped to 200. The hours should start
accruing again from 3/12/04 until they reach the 240
again, then stop.

The way I have it, is the formula in A6 is accruing from
the anniversay date and the formula in A7 says that if the
sum of cells A3 + A6 - A8 is => 240 then 240 otherwise A3
+ A6. So, when the hours drop below 240, it's adding A3 +
A6 which is the hours accrued from the anniversary date
which is incorrect. They should have what they accrued
from the anniversary date to 3/1/04 (when they reached the
cap) and then from 3/12/04 (when they dropped below the
cap) to the current accrual date. I don't know how to get
it to stop and then start again.

I hope I'm making sense, I appreciate you taking a look.