# PTO Accrual Spreadsheet help

1. ## PTO Accrual Spreadsheet help

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!!!

2. ## Re: PTO Accrual Spreadsheet help

Hi SP38555, you may want to post this in the regular Excel forum. Also, if you started on 1/1/2014, you would accrue 8 hours on
6/13/2014 (start date +90 days +73 days)
8/25/2014 (previous accrual date +73 days)
11/6/2014 (previous accrual date +73 days)
what do you do with the final accural for the year? The next one would be on 1/18/2015 (previous accrual date +73 days) but you stated they start over at 1/1/2015. So how do you hand these extra 18 days?

Steve=True

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1