Vacation Accrual and Tracking

Hello,
I am close to "novice" status when it comes to working with Excel formulas. I need to make in excel a sheet that does the following:

Tracks employees accrual of vacation time through the year
Allows for previous year's unused vacation to be carried forward into the current year
Allow for different accrual rates (employees who have worked 1 to 6 years accrue 5 hours every pay period, 7 to ? earn 6.67 and very long time employees earn 8.33)
Creates an alert (turns the box red which I currently have mine doing) when an employee reaches their max accrual and MUST take vacation. (1 to 6 max accrual is 120, 7 to ? is 160 and long time is 200)


I also need to be able to input vacation time taken as the year goes on and have it pull first from the previous vacation bank time and once that reaches 0 start taking that time from the current year's accrual to keep accurate track of how much time each employee has.

As an example:

I have worked for 7 years at the company which means I earn 6.67 hours vacation every pay period and there are two pay periods per month (1 to the 16th and 17th to the 31st). I have a banked time from 2014 of 34 hours carried forward. I have 68.36 vacation hours accrued for 2015. I will be taking vacation in August for 32 hours.

Can anyone help me?

I think that it would be easier to help if you could provide a desensitized file. I'd suggest that you select the number of years at which the accrual rate changes from 6 hrs 40 min to 8 hrs 20 min per pay period.

Try downloading "Attendance History 2015 A" from the site below.
It might be helpful to you.

HTML Code: