+ Reply to Thread
Results 1 to 5 of 5

Vacation Accrual

  1. #1
    Registered User
    Join Date
    11-07-2008
    Location
    Massachusetts
    Posts
    4

    Vacation Accrual

    Hi everyone,
    Thanks to all of you, Iíve made great progress in my vacation accrual spreadsheet, but have some new problems I hope you can help me out with because Iím at a total loss as to how to even start. Iíve attached my spreadsheet so you can take a look and hopefully give me some ideas.

    The first problem is that employees change their accrual rate based on their hire date, but vacation is accrued on a calendar year (Jan-Dec). For example, emp. # 6, Matthew Doeís hire date was 3/4/08. Based on that, he would accrue vacation at the 1 week rate (0.769) from Jan 1, 2009 through 3/3/09 and would accrue at the 2 week rate (1.539) from 3/4/09 through 12/31/09. How would I write formulas in the hours earned and # of hours columns for these situations?

    Along that same line, how would I write the formulas for new hires like emp. #18, Betty Doe who should only start accruing vacation as of her start date, but it calculates her as accruing since January 1?

    Also, we have several part-time folks who work anywhere from 4 to 6.5 hours per day 2, 3 or 4 days per week (that also may or may not change their accrual rate through the year). They would earn the same # of weeks based upon the hours they work Ė say emp. #17, Greg Doe only works 3 days per week, 5 hours per day. He would be accruing 2 weeks of vacation time, which would be 15 hours per week for a total of 30 hours, but heís also changing to the 3 week accrual schedule as of 5/10/09.

    Iím not very well versed in formula writing and the last two columns of the spreadsheet calculate time used and time remaining. The formulas I wrote are very basic (a2+c2+e2 etc) and I was wondering if there is a better way to do this that would be more accurate?

    Sorry for the long post, but I really wanted to make sure that I explained this as best I could. Iím at a complete loss as to how to even start this and any help at all would be much appreciated! Thanks everyone!
    Attached Files Attached Files

  2. #2
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    Re: Vacation Accrual

    I believe that you can solve this one with a user defined function.

    Please Login or Register  to view this content.
    To use this, open a new module in the VBA editor (Alt F11) and paste in the code.

    In the spreadsheet enter a formula such as

    Please Login or Register  to view this content.
    Hope this works and that the members of the Doe family enjoy their holidays.
    Martin

  3. #3
    Registered User
    Join Date
    11-07-2008
    Location
    Massachusetts
    Posts
    4

    Re: Vacation Accrual

    Thanks so much for your help, Martin (the Doe family thanks you also!) but I'm a little confused as I've never used VBA editor before and am not quite sure what to do with it? I hit alt f11, pasted the code, and entered the formula, but I get a name error? I'm not that fluent with excel and apologize for my inexperience - I'm sure I'm doing something wrong lol!

  4. #4
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    Re: Vacation Accrual

    Please see the attachment.

  5. #5
    Registered User
    Join Date
    11-07-2008
    Location
    Massachusetts
    Posts
    4

    Re: Vacation Accrual

    Thanks very much for all of your help!!

+ 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