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!!

2. ## 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

3. ## 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)

4. ## 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. ## 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. ## Re: Accrual Vacation Spreadsheet

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

7. ## 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.

8. ## 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.

9. ## 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

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