+ Reply to Thread
Results 1 to 9 of 9

Accrual Vacation Spreadsheet

  1. #1
    Registered User
    Join Date
    12-21-2016
    Location
    Columbis, SC
    MS-Off Ver
    version 1610
    Posts
    5

    Accrual Vacation Spreadsheet

    Hello!

    I have been tasked with making a spreadsheet to help out with keeping track of vacation days. I have researched all the other posts on here about this and am still beyond confused. I need the spreadsheet to restart the PTO days on the anniversary date and to apply a max of 10 days left over from the prior year.

    So right now this is the information I have.

    Years Worked PTO Hours PTO Days Accrual Rate
    1-2 years 80 10 3.333
    3 years 120 15 5.000
    4 years 160 20 6.667
    5 + years 200 25 8.333

    The sample Employee start dates I am using is:
    Employee 1- 08/15/16
    Employee 2- 07/01/10
    Employee 3- 01/24/07

    Thank you so much for any help!!
    Last edited by kfos; 12-21-2016 at 03:40 PM.

  2. #2
    Registered User
    Join Date
    12-21-2016
    Location
    Columbis, SC
    MS-Off Ver
    version 1610
    Posts
    5

    Re: Accrual Vacation Spreadsheet

    Years Worked PTO Hours PTO Days Accrual Rate
    1-2 years 80 10 3.333
    3 years 120 15 5.000
    4 years 160 20 6.667
    5 + years 200 25 8.333
    Last edited by kfos; 12-21-2016 at 12:57 PM. Reason: It keeps messing up the spacing

  3. #3
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    It changes constantly, but based in Ireland.
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    25,354

    Re: Accrual Vacation Spreadsheet

    Using a Table of values in a2 to D5, you could use this:

    =IFERROR(VLOOKUP(INT((TODAY()-$H2)/365),$A$2:$D$5,COLUMNS(I:I)+1,TRUE),0)
    Attached Files Attached Files
    Glenn



  4. #4
    Registered User
    Join Date
    12-21-2016
    Location
    Columbis, SC
    MS-Off Ver
    version 1610
    Posts
    5

    Re: Accrual Vacation Spreadsheet

    Sorry I shouldve put 0-2 years= 80 hours. But how can I make it that if say employee number 1 takes a vacation day today that it effects the total?

  5. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    It changes constantly, but based in Ireland.
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    25,354

    Re: Accrual Vacation Spreadsheet

    Amended for 0-2 years. However, I don't know what you want and where you want it any more...

    Will you please attach a sample Excel workbook? Please don't attach a picture of one.

    1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

    2. Make sure that your desired solution is also shown (mock up the results manually).

    3. Make sure that all confidential information is removed first!!

    4. Try to avoid using merged cells. They cause lots of problems!

    Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

  6. #6
    Registered User
    Join Date
    12-21-2016
    Location
    Columbis, SC
    MS-Off Ver
    version 1610
    Posts
    5

    Re: Accrual Vacation Spreadsheet

    I have attached the excel document I am trying to use.
    Attached Files Attached Files

  7. #7
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    2013 / 2016 / 365
    Posts
    7,068

    Re: Accrual Vacation Spreadsheet

    I did a couple of things with this sheet. I converted the ranges to tables.

    I established an employee list table mainly so I could use the employees as a drop down list for data validation.

    I established a holiday table so I could use WorkDay and NetWorkDay functions.

    I established a Vacation table to track when people actually take vacation. Type in the start date of the vacation in column A. Then select the employee and then type in the number of days. The Start Date is validated to be any day after the start of the current year. Employee name is a dropdown list from the employee table. Number of days is validated to a positive whole number.

    The End date is calculated using WorkDays, and an effective days is also calculated. In this case, Employee 2 starts the vacation on 12/30/2016 and is taking 5 days off. Only one of those days is a workday within the remaining year.

    I changed the Vacation Day lookup table to show the year "break points" - I can then use the table with VLOOKUP with the TRUE option.

    On the summary sheet, I look up the anniversary date from the employee table. I left the years of employment and Accrued from 2015 fixed. I look up max PTO using: =VLOOKUP(INT([@[Yrs. Employed]]),Table_Lookup,3,TRUE).

    Vacation used is =SUMIFS(Table_Vacation[Effective Days],Table_Vacation[Employee],[@Employee])

    And Remaining PTO is: =[@[Accrued from 2015]]+[@[Max PTO ]]-[@[Vaction Used]] - this assumes people can "borrow forward" on vacation time. Otherwise we will need the current full month and base the calculation on that.

    Also I did not prorate for if a person goes over 3 years during the year.

    These are refinements we can make as we flesh out the requirement.

    Also, I only did the first 5 employees.
    Attached Files Attached Files
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  8. #8
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    2013 / 2016 / 365
    Posts
    7,068

    Re: Accrual Vacation Spreadsheet

    I decided to take the extra steps. This version accounts for when an employee has an anniversary during the year and gets a bump in accrued days and computes accrued dates based on the current month.
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    12-21-2016
    Location
    Columbis, SC
    MS-Off Ver
    version 1610
    Posts
    5

    Re: Accrual Vacation Spreadsheet

    This spreadsheet looks amazing thank you so much! But I am having difficulties when I try to copy the formula for the months of 2017. Is there something else I have to do in the formula for the months of 2017 other than change which date the cell is referencing?

    2/1/17=VLOOKUP(YEARFRAC([@[Anniversary ]],P$1),Table_Lookup,4,TRUE) which equals 3.333
    3/1/17= =VLOOKUP(YEARFRAC([@[Anniversary ]],Q$1),Table_Lookup,4,TRUE) equals 5

    which isnt right because this person started in 2/17/16 so their hours should be 3.333 until 2018

+ 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. Vacation accrual spreadsheet
    By thesar805 in forum Excel General
    Replies: 7
    Last Post: 09-04-2017, 06:52 PM
  2. Vacation Accrual and Rollover Spreadsheet
    By garzalore in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-28-2016, 06:02 PM
  3. Replies: 1
    Last Post: 09-24-2015, 03:22 PM
  4. Replies: 4
    Last Post: 01-20-2015, 11:51 AM
  5. Employee Vacation / Holiday accrual spreadsheet
    By dzidek in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 07-24-2014, 05:35 PM
  6. [SOLVED] Need Simple Vacation Accrual Spreadsheet
    By mrbsbest in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 08-29-2013, 10:18 PM
  7. Vacation Accrual Spreadsheet
    By jrace in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 05-16-2013, 05:28 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