+ Reply to Thread
Results 1 to 4 of 4

Vacation Accrual Formula

  1. #1
    Registered User
    Join Date
    11-20-2013
    Location
    Amherst, Ohio
    MS-Off Ver
    Excel 2007
    Posts
    2

    Vacation Accrual Formula

    Need help on what formula to use for the following:

    I need to do a spreadsheet on vacation accrual that goes like this:
    Year of starting date, but the accrual is 1 year but less than 7 accum 144 hours; 7 years but less than 14 will accum 216; 15 but less than21 will accum 288; and 21 years or more will accum 360. Can you help me with a formula, please?

  2. #2
    Forum Guru Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,267

    Re: Vacation Accrual Formula

    Try this

    =INDEX({0,144,216,288,360},MATCH(DATEDIF(start_Date,TODAY(),"Y"),{0,1,7,14,21,99},1))

  3. #3
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,904

    Re: Vacation Accrual Formula

    Or this:

    Copy and paste formula in B2 and drag it down

    =LOOKUP(DATEDIF(A2,TODAY(),"y"),{0,1,7,14,21,99},{0,144,216,288,360})


    A
    B
    C
    1
    Date Vacation Accrual Years
    2
    3/15/2000
    216
    13
    3
    2/13/1991
    360
    22
    4
    2/14/1985
    360
    28
    5
    3/15/2006
    216
    7
    6
    3/16/2009
    144
    4
    7
    4/11/2011
    144
    2
    8
    5/5/2013
    0
    0
    9
    2/13/1992
    360
    21
    10
    1/17/1999
    288
    14
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  4. #4
    Registered User
    Join Date
    11-20-2013
    Location
    Amherst, Ohio
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Vacation Accrual Formula

    Thank you. Problem solved!

+ 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: 3
    Last Post: 10-16-2013, 09:05 PM
  2. Formula for vacation accrual
    By zandiago1 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-20-2013, 09:48 PM
  3. Vacation Accrual Formula?
    By denizenXIX in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 01-28-2013, 02:06 PM
  4. Vacation Accrual Formula
    By Cullen8 in forum Excel General
    Replies: 3
    Last Post: 03-22-2012, 04:31 PM
  5. Please help!! Vacation Accrual Formula
    By MissNadine in forum Excel Formulas & Functions
    Replies: 20
    Last Post: 09-06-2005, 07:05 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