PTO Calculator

[phoenix guy]

What a great resource...glad I found this community.

I am creating a time off calculator for company I work for.

We use a pretty standard PTO methodology that gives employees 2 days annually as "Personal Days" on the anniversary date with additional PTO being accrued on a weekly basis throughout the year.

The PTO scale is sliding based upon employment duration.

I have most of the spreadsheet completed...but need help with the PTO Accrued to date calculation. The formula is (Number of weeks since last anniversary x accrual rate) + 16 hours of annual personal days. I am unsure how to get Excel to quanitify the weeks and how to get it to identify the accrual rate.
[TABLE="width: 1266"]
<tbody>[TR]
[TD]Annual Personal Days[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 4"]Employees automatcially receive 2 personal days on each anniversary date.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Years Employed[/TD]
[TD]Days[/TD]
[TD]Hours[/TD]
[TD]Accrual Rate[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PTO Accrual Rates[/TD]
[TD]1st Year[/TD]
[TD]10[/TD]
[TD]80[/TD]
[TD]1.53846[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2nd and 3rd[/TD]
[TD]15[/TD]
[TD]120[/TD]
[TD]2.30769[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]4th and 5th[/TD]
[TD]18[/TD]
[TD]144[/TD]
[TD]2.76923[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]6th+[/TD]
[TD]21[/TD]
[TD]168[/TD]
[TD]3.23077[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 3"]§ Employees are eligible for Holiday Pay upon hire.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 6"]§ New employees begin accrual of PTO from day one but use is not permitted until the 90 probation period is completed.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 4"]§ Personal days are not available for use until 90 days of service is completed.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 4"]§ Employees can carry over a maximum of 60 hours PTO from the previous year.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 4"]§ Unused PTO is payable at termination of employment up to 80 hours accrued.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Employee[/TD]
[TD]Hire[/TD]
[TD]Todays[/TD]
[TD]Years[/TD]
[TD]Hours[/TD]
[TD]PTO Accrued[/TD]
[TD]PTO Hours[/TD]
[TD]PTO Days[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]Date[/TD]
[TD]Date[/TD]
[TD]Worked[/TD]
[TD]Used[/TD]
[TD]To Date[/TD]
[TD]Available[/TD]
[TD]Available[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]12/13/2013[/TD]
[TD]10/18/2013[/TD]
[TD]0[/TD]
[TD]8[/TD]
[TD]1) *See Notation Below[/TD]
[TD]#VALUE![/TD]
[TD="align: center"]#VALUE![/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]8/25/2013[/TD]
[TD]10/18/2013[/TD]
[TD]0[/TD]
[TD]32[/TD]
[TD][/TD]
[TD]-32[/TD]
[TD]-4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]9/12/2013[/TD]
[TD]10/18/2013[/TD]
[TD]0[/TD]
[TD]16[/TD]
[TD][/TD]
[TD]-16[/TD]
[TD]-2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]10/10/2005[/TD]
[TD]10/18/2013[/TD]
[TD]8[/TD]
[TD]24[/TD]
[TD][/TD]
[TD]-24[/TD]
[TD]-3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]8/22/2012[/TD]
[TD]10/18/2013[/TD]
[TD]1[/TD]
[TD]20[/TD]
[TD][/TD]
[TD]-20[/TD]
[TD]-2.5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]11/19/2009[/TD]
[TD]10/18/2013[/TD]
[TD]4[/TD]
[TD]16[/TD]
[TD][/TD]
[TD]-16[/TD]
[TD]-2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]7/14/1997[/TD]
[TD]10/18/2013[/TD]
[TD]16[/TD]
[TD]12[/TD]
[TD][/TD]
[TD]-12[/TD]
[TD]-1.5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]8/5/2013[/TD]
[TD]10/18/2013[/TD]
[TD]0[/TD]
[TD]48[/TD]
[TD][/TD]
[TD]-48[/TD]
[TD]-6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]6/13/2012[/TD]
[TD]10/18/2013[/TD]
[TD]1[/TD]
[TD]80[/TD]
[TD][/TD]
[TD]-80[/TD]
[TD]-10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]9/27/2010[/TD]
[TD]10/18/2013[/TD]
[TD]3[/TD]
[TD]60[/TD]
[TD][/TD]
[TD]-60[/TD]
[TD]-7.5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]11/7/2012[/TD]
[TD]10/18/2013[/TD]
[TD]1[/TD]
[TD]64[/TD]
[TD][/TD]
[TD]-64[/TD]
[TD]-8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]6/28/2012[/TD]
[TD]10/18/2013[/TD]
[TD]1[/TD]
[TD]24[/TD]
[TD][/TD]
[TD]-24[/TD]
[TD]-3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]4/2/2013[/TD]
[TD]10/18/2013[/TD]
[TD]0[/TD]
[TD]8[/TD]
[TD][/TD]
[TD]-8[/TD]
[TD]-1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]12/5/2012[/TD]
[TD]10/18/2013[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD][/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]12/23/2002[/TD]
[TD]10/18/2013[/TD]
[TD]11[/TD]
[TD]80[/TD]
[TD][/TD]
[TD]-80[/TD]
[TD]-10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]6/21/2011[/TD]
[TD]10/18/2013[/TD]
[TD]2[/TD]
[TD]120[/TD]
[TD][/TD]
[TD]-120[/TD]
[TD]-15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]6/24/2013[/TD]
[TD]10/18/2013[/TD]
[TD]0[/TD]
[TD]12[/TD]
[TD][/TD]
[TD]-12[/TD]
[TD]-1.5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 7"]1) *This calculation is as follows: (Number of weeks since last anniversary x accrual rate) + 16 hours of annual personal days.[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]