Help with IF formula

[Phyllis Bogart]

I am re-posting this from Friday. I didn't get a response
and am not sure if it's too difficult or too much to ask.
I would appreciate any advice. Thanks.

An employee is eligible for 120 hours of vacation per
year, accrued from their date of hire or anniversary
date. They can carry over unused vacation time from one
year to the next. However, once they've reached two times
their eligible vacation hours,they stop accruing until
they drop below, in this case, 240 hours. Once their total
drops below 240 then it will start to accrue again. Once
they stop accruing, they do not earn any vacations hours,
they cannot go back and recoup any hours lost due to
reaching their hours cap. 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-A2)/7*2.3076,0)(accrued hours from
anniversary date)
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,
because it's including hours that should not have accrued
because of reaching their cap. 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.

 

[C]

Why not do it this way Phyllis....

Keep the data in cells A1-A2. In A3, move the Accrual Date
to this field.
A4- Put in or link the Hours carried over from previous
year.
A5- Leaves Used in Current Year or Usage in Current Year
A6- Total Accrual with your formula "=((A3-A2)/7)*2.3076
A7- Net Accrued Leaves with formula "=A6-A5-A4"
A8- Net Available Leaves with formula "=if
(A7>240,"240",+A7)"

You can use the A7 and A8 formula like this or you can
omit A7 line and just compound
the formula in A7 by using this formula "=if(A6-A5-
A4>240/,"240",A6-A5-A4)"

My suggestion is to go with the separate formulas in A7 &
A8 just so both numbers can be
compared.

Will this work?
C

 

[Phyllis]

Thank you for your response. I tried your suggestion,
however I used in my formulas (A3+A5-A4), as A5 represents
carried over hours that should be added to the current
accrual, then the used hours should be backed out. I
believe your formula is doing the same thing as mine, in
that when the hours drop below 240, it's adding all
accrued hours from the anniversary date to the accrual
date, which is incorrect. Once the cap is reached accrual
should stop. Our formulas only stop adding the accrued
hours, it doesn't stop accruing them. But, thank you I
really appreciate your assistance and input.