+ Reply to Thread
Results 1 to 3 of 3

vacation accrual formula based on previous year avg hours worked

  1. #1
    Registered User
    Join Date
    06-24-2009
    Location
    Springfield, MO
    MS-Off Ver
    Excel 2007
    Posts
    1

    Unhappy vacation accrual formula based on previous year avg hours worked

    We have a client that has a complicated vacation policy and I just can't get my brain wrapped around how to write this one out. Any direction would be appreciated.

    1) Employees must have been employed at least 1 year before becoming eligible for vacation.
    2) On their anniversary date (every year), if they have worked an average of at least 30 hours a pay period for the year, then they receive an accrual equal to that average, with a max of 40 hours. (Formula for that would be total hours for year/52 weeks - does not take into consideration how many checks those hours were received over)
    3) If that anniversary date has not occured yet, the most recent anniversary date has to be used (if the anniversary date is 10/01 and the hire year was 2004, then to determine vacation accrual right now, the records from 10/01/2007 to 9/30/2008 must be used to calculate the average hours)
    4) Unused hours reset to 0 on the anniversay date.
    5) Calculations also must be made going forward from the anniversary date to accrue any potential vacation.


    I am assuming that I can do most of this in VBA (we have Excel 2007). I have attached a data sample.


    Thanks for any and all suggestions! This is driving me crazy!
    LFox
    Attached Files Attached Files
    Last edited by lfox; 06-25-2009 at 01:46 PM.

  2. #2
    Registered User
    Join Date
    07-08-2009
    Location
    Texas
    MS-Off Ver
    Excel 97
    Posts
    1

    Re: vacation accrual formula based on previous year avg hours worked

    This sounds very similar to what I am needing. Have you had any luck in perfecting this formula. I cannot open the attached file you have on there. Any info would be appreciated.

    Thanks,

  3. #3
    Registered User
    Join Date
    11-05-2009
    Location
    milwaukee
    MS-Off Ver
    Excel 2003
    Posts
    35

    Re: vacation accrual formula based on previous year avg hours worked

    for me i just need their anniversery date when reached, to reset their vacation hours based on years work

    any ideas? lol
    http://www.pcreview.co.uk/forums/thread-1024373.php

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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