I am trying to set up a spreadsheet to track our company's vacation accruals. The way our policy works is we accrue hours every pay check and we get paid every other Thursday. After one year of employment you will have earned 40 hours vacation, after 2 years of employment you will have earned 80 hours vacation, etc. The breakdown of the hours accrued are as follows:

0-2 years: 1.54 hours per pay check
2-7 years: 3.08 hours per pay check
7-14 years: 4.62 hours per pay check
14+ years: 6.15 hours per pay check

Our vacation policy is also based off of anniversary date. So for example, John started 08/01/13 so he already has 80 hours available to him, plus he has already accrued 33 hours from 08/01/15-01/05/16. I can't seem to get any formula to reflect this.

Thanks,