Vacation Tracking & Accruals

Hello! New to the forum and kind of desperate for help! I'm trying to combine two spreadsheets for vacation tracking and accruals.

To start, our vacation accruals are as follows:

0-1 years: 40 hours (5 days)
1-5 years: 80 hours (10 days)
5+ years: 120 hours (15 days)

"Employees will be entitled to one full week (40 hours) of paid vacation prorated as earned in their first year of employment starting the first of the month following the start date."
"Employees who have worked a full calendar year will be entitled to two full weeks (80 hours) of paid vacation."
"Employees who have worked five full years will be entitled to three full weeks (120 hours) of paid vacation."
"Vacation must be taken in the calendar year allotted, and may not be carried over into the next calendar year."

I've attached a spreadsheet with two tabs, one where we track vacation time(by days) and the other I just started with basic accrual calculations. I would like it to somehow feed off each other. If there are days taken in the first tab, that it's deducted from the second tab(and even into negative accrual amounts as in unearned time). The other thing would be to show how much is accrued up to today's date. For example, we don't do carryovers on vacation time and said employee accrues at a 3.33 rate every month. Is there a formula to add up accruals since 01/01/16 or would I have to have a table of every month and their accruals with a SUM formula? Sorry if confusing but hard to explain! Any starting off point would be helpful! Thank you!!

Working through this, but immediate problem is your Accruals sheet D12 - F12.

The "Vaca Accrual Rate per Weekly Pay Period" divides the numbers by twelve, suggesting they are "monthly" accruals? Weekly should divide by 52 (ignoring Leap Years for the moment).

Ochimus

Thank you for your help!!! I hadn't yet decided if I wanted it to be weekly accruals or monthly. Our employee handbook implies monthly but the employees are paid weekly.