Hello everyone,

I've been working on this for a while and trying different styles... I need to develop a tracking spreadsheet (for 50 employees) that will calculate accruing vacation time and vacation time used based on the following parameters:

Employees who have worked less than 3 years but past the 90 day anniversary with the company (at which they start accruing) (there is one more caveat on this... the accrual day is the 1st of the month of the month past the 90 days)[I figure this out on my excel sheet] at:
- 4 hours a month, at the end of the month, with a MAX cap of 72 hours that they can have stored (but if they use vacation time and fall below the 72 hours they can continue to accrue again up to 72 hours...)

Employees who have worked more than 3 years but less than 6 years with the company carry over their prior vacation and begin to accrue from here onward at:
- 6.8 hours a month, at the end of the month, with a MAX cap of 122.4 hours that they can have stored (but if they use vacation time and fall below the 122.4 hours they can continue to accrue again up to 122.4 hours...)

Employees 6 years and over with the company carry over their prior vacation and begin to accrue from here onward at:
- 10 hours a month, at the end of the month, with a MAX cap of 180 hours that they can have stored (but if they use vacation time and fall below the 180 hours they can continue to accrue again up to 180 hours....)

& yes, I need to be able to deduct vacation time used.

Does anyone have any suggestions for layout or for a formula that can do part of these functions? I appreciate any advice!

I have created a test sheet, and was accruing based on these conditions for the first and started on the second set of rules (for years 3+).

However when I accrue to the max of 72 hours on the first policy, it no longer accrues correctly if they used vacation and fall under the 72 hours cap again.

I know this is a overcomplicated policy, but that is what the company wants and they will not budge.... Any help or advice is appreciated. I know my sheet isn't great.. but I'm trying options.