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

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

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)

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.

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.

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.