Vacation Accrual

[Phyllis]

I am re-posting this from Friday & Monday. I got a
response, but it didn't solve my problem. 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.

 

[Michael J. Malinsky]

I don't believe what you want can be done without addint additional lines to
your spreadsheet. In your current spreadsheet, there is no way for Excel to
know what happened in prior years related to whether vacation time stopped
accruing. It sounds as if you need more of a database solution for this
that would be able to keep historical information.

If you wanted to use Excel, you would have to add a line showing total hours
accrued up to the prior year end then add what should be accrued for the
current year. I suppose you could also maintain separate sheets for each
employee and add a new column for each year (from the employee hire date)
that would calculate what you want.

Email me directly if you need any additional assistance on this matter.

Mike.


--
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