+ Reply to Thread
Results 1 to 8 of 8

Vacation accrual spreadsheet

  1. #1
    Registered User
    Join Date
    10-11-2013
    Location
    Ventura, CA
    MS-Off Ver
    Excel 2007
    Posts
    3

    Vacation accrual spreadsheet

    I am looking for a sample spreadsheet to use to figure Vacation accrual. We have about 50 employees, so I would like a one-page workbook!

    For every employee, years 1-5 of employment, accrue 80 hours
    Years 6-10, accrue 120 hours
    Years 10+, accrue 160 hours

    I have tried several, and searched here and cannot find one that is a good fit. I just need it to accrue based on Date of Hire, and have a column for used. Any ideas out thyere??? Thank you in advance,.

  2. #2
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,468

    Re: Vacation accrual spreadsheet

    Does your Vacation expire every year or does it compound into the next year?

  3. #3
    Registered User
    Join Date
    10-11-2013
    Location
    Ventura, CA
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Vacation accrual spreadsheet

    It rolls over into the next year, however, here are the Max hours they can have:
    Year 1-5 : 100 hours
    Year 6-10 : 150 hours
    Years 11+ : 200 hours

    My main concern is tracking the accruals according to date of hire, not so much the max balances, but any thoughts will help!!!

  4. #4
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,468

    Re: Vacation accrual spreadsheet

    Here's one I developed last month. It uses a table to look up the time and assigns it to each person.

    I updated it with your criteria.

    You can change the hire dates, names, last year's leftover hours and used time so far this year. The formulas take care of the rest.
    • allotment autofills by hire date
    • you enter last year's remaining hours
    • subtotal: this autofills by default to max hours if allotment + last year's rollover exceeds the max
    • enter used hours
    • remaining vacation: autofills with annual subtotal - used


    vacation formula.xlsx
    Last edited by daffodil11; 10-11-2013 at 04:46 PM.

  5. #5
    Registered User
    Join Date
    10-11-2013
    Location
    Ventura, CA
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Vacation accrual spreadsheet

    Awesome! I'm going to mess around with this back at work Monday I'll let ya know how it goes!!! Thanks a ton!

  6. #6
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,468

    Re: Vacation accrual spreadsheet

    No problem, glad I could help out.

  7. #7
    Registered User
    Join Date
    09-04-2017
    Location
    cape cod, ma
    MS-Off Ver
    10
    Posts
    11

    Re: Vacation accrual spreadsheet

    Hi, I know this is an older thread.

    I used your vacation formula template to use for my company. Yet, I had to change the accrual rates. 1 year- 40 hrs, 2 years- 80 hrs. 8 years- 120 hrs, 15 years- 160 hours. The problem is, when I try and change the formula to line 8 from 7, it comes up as an error as "value".

    Any thoughts on this?

  8. #8
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,112

    Re: Vacation accrual spreadsheet

    caitline23,Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
    Ben Van Johnson

+ 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. [SOLVED] Need Simple Vacation Accrual Spreadsheet
    By mrbsbest in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 08-29-2013, 10:18 PM
  2. Vacation Accrual Linking Spreadsheet Formula
    By RedWings6 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 06-25-2013, 01:40 PM
  3. Vacation Accrual Spreadsheet
    By jrace in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 05-16-2013, 05:28 PM
  4. Vacation Accrual
    By kanolan in forum Excel General
    Replies: 4
    Last Post: 04-27-2009, 04:55 PM
  5. Vacation Accrual
    By kanolan in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-09-2009, 02:56 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