+ Reply to Thread
Results 1 to 2 of 2

Help with excel formula for accruing vacation with different rates of accrual

  1. #1
    Registered User
    Join Date
    09-14-2018
    Location
    Fountain Valley, CA
    MS-Off Ver
    10
    Posts
    1

    Help with excel formula for accruing vacation with different rates of accrual

    Hello, I am looking for a formula I can use in excel that will calculate the accrual of vacation time with the following rules:

    The annual PTO allowances for Full-Time employees by years of service are as follows:
    Date of Hire Through completion of 2 years = 8 Days (64 hours)
    3 years through completion of 7 years = 13 days per year (104 hours)
    8 years or more = 18 days per year (144 hours)

    Eligible PTO days will be accrued as follows for work performed (not including paid holidays or vacation):
    Date of hire through the 3rd month: 13.34 hours per month
    4th month through the completion of 1years: 2.67 hours per month 1year through the completion of2 years: 5.34 hours per month
    3 years through the completion of 7 years: 8.67 hours per month 8 years or more: 12 hours per month

    The maximum carry over (cap) hours an employee may maintain in their balance before they stop accruing is 40 hours more than their annual allowed.
    Date of hire through completion of 2 years: 104 hour cap 3 years through completion of7 years: 144 hour cap
    8 years or more: 184 hour cap

    Eligible PTO days will be accrued as follows for work performed (not including paid holidays or vacation):
    Date of hire through the 3rd month: 13.34 hours per month
    4th month through the completion of 1years: 2.67 hours per month
    1year through the completion of2 years: 5.34 hours per month
    3 years through the completion of 7 years: 8.67 hours per month 8 years or more: 12 hours per month

    The maximum carry over (cap) hours an employee may maintain in their balance before they stop accruing is 40 hours more than their annual allowed.
    Date of hire through completion of 2 years: 104 hour cap 3 years through completion of7 years: 144 hour cap
    8 years or more: 184 hour cap

    If anyone knows how to calculate weekly accrual, that would be really helpful as that is how my new company has been tracking PTO.

    Thank you in advance!

    Tara T-

  2. #2
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Help with excel formula for accruing vacation with different rates of accrual

    Hello tara97 and Welcome to Excel Forum.
    Here is a spreadsheet that was developed by one of our contributors, daffodil11 (see post #4 in this thread for more details), which I have modified based on the annual PTO allowance and carry over stated. As for the monthly and weekly accrual, I feel as if we need to see a manually prepared example showing expected out put as it relates to weekly accrual. We can than attempt to write formulas and/or code to automate the process of producing the output.
    To upload a sample workbook (not a picture or pasted copy) click on the GO ADVANCED button below the Quick Reply window and then scroll down to Manage Attachments to open the upload window.
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ 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. Replies: 4
    Last Post: 01-20-2015, 11:51 AM
  2. Excel formula vacation accrual from hire date, reset
    By Daphne in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-13-2013, 05:23 AM
  3. [SOLVED] Vacation Accrual Formula
    By cathlene68 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-20-2013, 02:24 PM
  4. Replies: 3
    Last Post: 10-16-2013, 09:05 PM
  5. Replies: 1
    Last Post: 10-12-2013, 10:27 PM
  6. Excel formula vacation accrual from hire date, reset
    By d.gomez2013 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-11-2013, 02:10 PM
  7. Vacation Accrual Formula
    By Cullen8 in forum Excel General
    Replies: 3
    Last Post: 03-22-2012, 04:31 PM

Bookmarks

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