I am trying to come up with a formula for PTO accruals, using "As of Dates". I can see in my head how to make this work, I'm just unable to translate that into excel. :/ This is for a company of over 100 employees, therefore, having it all on one sheet would be ideal. I thought of having 2 separate sheets (one for employees <1 year and the other for employees > 1 year) if that could make it any more feasible. Anyway, hopefully I can explain this easily enough...

Personal days are accrued at a rate of 8 hrs per 73 days starting on your 90th day of employment with the company. After the 1st calendar year of employment, they accrue as of January 1. Personal days cannot be carried over.

Vacation days are accrued at 40 hrs per year accrued on your anniversary date starting on your 1 year anniversary and you earn an additional 8 hrs per year thereafter up to a maximum of 15 days(120 hrs). (John Doe: hired 6/6/2012 -- 6/6/13 = 40 hrs, 6/6/14 = 48 hrs) Vacation days can be carried over year to year so long as it does not exceed the 15 day (120 hrs) maximum.

The formula will not work if it accrued by weekly amounts, monthly amount and so forth. I need to be able to pull up the spreadsheet and it show me how many are available as of current date for each employee.

Any ideas??? Help is GREATLY appreciated!!!