+ Reply to Thread
Results 1 to 2 of 2

PTO Accrual Spreadsheet help

  1. #1
    Registered User
    Join Date
    Tennessee, USA
    MS-Off Ver

    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. #2
    Forum Contributor
    Join Date
    MS-Off Ver
    Excel 2007

    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?


+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Vacation accrual spreadsheet
    By thesar805 in forum Excel General
    Replies: 7
    Last Post: 09-04-2017, 06:52 PM
  2. PTO Accrual Spreadsheet
    By bnickelsun in forum Excel General
    Replies: 14
    Last Post: 09-19-2014, 07:19 PM
  3. Vacation Accrual Spreadsheet
    By jrace in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 05-16-2013, 05:28 PM
  4. Simple PTO Accrual Spreadsheet
    By jkalhorn in forum Excel General
    Replies: 1
    Last Post: 06-20-2012, 04:30 PM
  5. PTO Accrual Spreadsheet
    By JTPhillips in forum Excel General
    Replies: 4
    Last Post: 02-23-2012, 05:13 PM

Tags for this Thread


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