Vacation Accruals

I'm trying to develop a worksheet that will return the correct vacation accruals for employees terminating.

1. We are paid semi monthly - on the 15th and last day of the month.
2. Employees hired in the current year do not start accruing time until the first payroll folllowing 90 days of employment (ie employee hired 2/16 would not accrue vacation time until the payroll dated May 31).
3. The attached spreadsheet shows how the accruals are calculated. Refering to the employee hired in Feb above, this employee would earn/accrue time starting from May 31 thru the end of the year - 15 payperiods * 3.33 hours = 49.95 total vacation hours.
4. At 5 years, the accrual rate increases as it also does at 15 years. However, the higher rate is not considered until the first payroll following the employee's 5th or 15th year anniversary. So, using the Feb 16, 2010 new hire - flash forward 5 years, in 2015, the employee would earn 3.33 hours for 3 payperiods (Jan and 1/2 of Feb) and then, starting from the end of Feb, would earn 5 hours per pay period. This employee would net 114.99 hours for the year of 2015.

I want this worksheet to be able to calculate the total hours the employee is eligible for during the year that they are being terminated. In the second section, HR will indicate how many complete payrolls have elapsed, how many hours the employee has worked in the current pay period and how many vacation hours the employee has used year to date (no rollover).

I'm struggling with computing these numbers for employees termed in the same year in which they were hired and also in calculating actual time accrued for when they go over 5 or 15 years.

Any help would be much appreciated.

This answered my question perfectly, Thank you for your help!