+ Reply to Thread
Results 1 to 5 of 5

Challenging Vacation Accrual

  1. #1
    Registered User
    Join Date
    05-07-2014
    Location
    San Francisco, CA
    MS-Off Ver
    Excel for Mac 2011
    Posts
    3

    Challenging Vacation Accrual

    Hopefully someone out there is interested in helping me figure a way to calculate the amount someone has accrued at anyone time.

    My small non profit has been around for a very long time and one of our most senior employees just left. She filed a claim saying her vacation payout, in good faith, was wrong. My Execs asked me to help the finance manager figure out if and where the calculations went wrong. I know excel is the key to breaking down this complex situation but I am not super skilled at the formulas, at best I figure out ways to work around. Here is the Challenge:

    Accrual Rates:
    Less than 2 years - 1/16th of a days pay for each day worked to a maximum of 15 days; if an employees works at least 215 days he/she will accrue 15 days of vacation.
    2 to 5 years - 1/12th of a days pay for each day worked to a maximum of 20 days; if an employees works at least 210 days he/she will accrue 20 days of vacation.
    5 years or more - 1/9th of a days pay for each day worked to a maximum of 25 days; if an employees works at least 205 days he/she will accrue 25 days of vacation.

    Accrual Limit:
    We cannot accrue more than 2 times our our annual entitlement.

    Here's what I attempted but it doesn't account for the holidays

    We go from anniversary to anniversary and accrue on vacation days, sick days and holidays.


    What is throwing me for a loop is the Accrual cap. If some one is capped and uses their sick days at the beginning of the year they can potentially earn those days back. If the days are used just before the person cashes out then they would have earned it back.

    Here is what I attempted but it doesn't account of holidays

    Attachment 316786

  2. #2
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Challenging Vacation Accrual

    Your attachment isn't any good. A workbook with description of the problem is much better than just descriptions or pictures.

    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  3. #3
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Challenging Vacation Accrual

    You describe rates of accrual and caps for vacation leave then you switch to sick leave. Are the rates and caps the same?

  4. #4
    Registered User
    Join Date
    05-07-2014
    Location
    San Francisco, CA
    MS-Off Ver
    Excel for Mac 2011
    Posts
    3

    Re: Challenging Vacation Accrual

    I am only talking about vacation day accruals. But an employee accrues vacation even when they are sick or on holiday pay. There is no before and after there is only my feeble attempt at calculating a formula.

    Here is a spreadsheet attached.

    Example.xlsx

  5. #5
    Registered User
    Join Date
    05-07-2014
    Location
    San Francisco, CA
    MS-Off Ver
    Excel for Mac 2011
    Posts
    3

    Re: Challenging Vacation Accrual

    also, THANK YOU.

+ 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. Please help with vacation accrual
    By RADD69@EARTHLINK.NET in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 04-11-2013, 05:12 PM
  3. Need help for vacation accrual
    By ecastaneda in forum Excel General
    Replies: 2
    Last Post: 04-18-2012, 08:31 PM
  4. Vacation Accrual
    By raemags in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-21-2011, 07:56 PM
  5. Vacation Accrual
    By kanolan in forum Excel General
    Replies: 4
    Last Post: 04-27-2009, 04:55 PM

Tags for this Thread

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