Vacation Hour Formula

[Gtrtim112]

PART 1

I currently have a spreadsheet that I use for employee vacations. Our vacations are based on hire dates in lieu of the calendar year. Currently, the following formula is in place to properly figure the employee's eligible balance.

=IF(E2="FT",LOOKUP(DATEDIF(C2,TODAY(),"y"),{0,1,2,9,18;0,40,80,120,160}),0)

This basically states that if the employee is full time (hence FT), they will receive vacation time based on their hire date and today's date. 1 year will get them 40 hours, 2 years gets them 80 hours, etc.

Our policy is changing so my formula will need to also. The only difference is that now, FT employees will receive 24 hours of vacation after 90 days. The rest stays the same. The one sidenote on this is that it DOES NOT include supervisors, which are salaried employees, only hourly. In my attached spreadsheet. So, I'll need the formula to exclude anyone who is a supervisor... this information is noted in a column on the attached spreadsheet.

Part 2

We also have personal days. These are based off of calendar year and the formula I currently use is..

=IF(AND(E2="FT",YEAR(C2)<YEAR(TODAY()),D2<>"Supervisor"),16,0)

This basically states that anyone that is full time, with the exception of supervisors, will receive 16 hours of personal days at the beginning of each year.

Our policy on this is changing also. The difference now is that we will also give part time (PT) DOCK employees 24 hours of personal days. This 24 hours is accrued in one hour increments. For every 30 hours worked, they gain 1 hour of personal day time. I currently don't have a column for "Hours Worked" but can add one.

****************************

So, I know that's a lot to ask for but I've yet to stump anyone in these message forums. Every time I've posted something difficult, someone has been able to figure it out. I'm hoping for the same here.

I've attached my example spreadsheet.

Thanks for taking a look!