Challenging Vacation Accrual

Hopefully someone out there is interested in helping me figure a way to calculate the amount someone has accrued at anyone time.

My small non profit has been around for a very long time and one of our most senior employees just left. She filed a claim saying her vacation payout, in good faith, was wrong. My Execs asked me to help the finance manager figure out if and where the calculations went wrong. I know excel is the key to breaking down this complex situation but I am not super skilled at the formulas, at best I figure out ways to work around. Here is the Challenge:

Accrual Rates:
Less than 2 years - 1/16th of a days pay for each day worked to a maximum of 15 days; if an employees works at least 215 days he/she will accrue 15 days of vacation.
2 to 5 years - 1/12th of a days pay for each day worked to a maximum of 20 days; if an employees works at least 210 days he/she will accrue 20 days of vacation.
5 years or more - 1/9th of a days pay for each day worked to a maximum of 25 days; if an employees works at least 205 days he/she will accrue 25 days of vacation.

Accrual Limit:
We cannot accrue more than 2 times our our annual entitlement.

Here's what I attempted but it doesn't account for the holidays

We go from anniversary to anniversary and accrue on vacation days, sick days and holidays.


What is throwing me for a loop is the Accrual cap. If some one is capped and uses their sick days at the beginning of the year they can potentially earn those days back. If the days are used just before the person cashes out then they would have earned it back.

Here is what I attempted but it doesn't account of holidays

Attachment 316786

I am only talking about vacation day accruals. But an employee accrues vacation even when they are sick or on holiday pay. There is no before and after there is only my feeble attempt at calculating a formula.

Here is a spreadsheet attached.

Example.xlsx

also, THANK YOU.