+ Reply to Thread
Results 1 to 6 of 6

Vacation Accrual Help Please

  1. #1
    Registered User
    Join Date
    05-03-2013
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    4

    Vacation Accrual Help Please

    I am having difficulty writing a vacation accrual formula.

    Basically I want the formula to start accruing from an employees hire date. If I use a Today() function I want to know how many hours an employee has accrued
    0-1 years - .77 week accrual
    2-9 years 1.54 week accrual
    10-14 years 2.31 week accrual
    15+ Years 3.08 week accrual.
    The employee should jump to the next level of accrual when their anniversary date hits and carry the total accrued from before anniversary date added in.

    For example if my employee was hired 01/01/2012 and today was 01/08/2013 then employee would have accrued (.77*52) + (1.54*1) = 41.58 Hours

  2. #2
    Valued Forum Contributor loginjmor's Avatar
    Join Date
    01-31-2013
    Location
    Cedar Rapids, Iowa
    MS-Off Ver
    Excel 2013
    Posts
    1,073

    Re: Vacation Accrual Help Please

    Hi -

    Try the attached spreadsheet.

    Hope that helps.
    Attached Files Attached Files
    ____________________________________________
    If this has solved your problem, please edit the thread title to add the word [SOLVED] at the beginning. You can do this by
    -Go to the top of the first post
    -Select Thread Tools
    -Select Mark thread as Solved

    If I have been particularly helpful, please "bump" my reputation by pressing the small star in the lower left corner of my post.

  3. #3
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Vacation Accrual Help Please

    If you have start date in A2 and today's date in B2 then this formula will give you the total hours accrued

    =(SUMPRODUCT((DATEDIF(A2,B2,"y")>{0,1,9,14})*52,DATEDIF(A2,B2,"y")-{0,1,9,14})+MATCH(DATEDIF(A2,B2,"y"),{0,1,9,14})*INT(DATEDIF(A2,B2,"yd")/7))*0.77
    Audere est facere

  4. #4
    Registered User
    Join Date
    05-03-2013
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Vacation Accrual Help Please

    Thank you very much!

  5. #5
    Registered User
    Join Date
    05-03-2013
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Vacation Accrual Help Please

    I have a question how do I account to accrue for current year only though but based on the rate by the years of service. For example if you are here 20+ you should start out accruing at 3.04

  6. #6
    Registered User
    Join Date
    05-03-2013
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Vacation Accrual Help Please

    I have a question how do I account to accrue for current year only though but based on the rate by the years of service. For example if you are here 20+ you should start out accruing at 3.04

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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