phoenix guy
New Member
- Joined
- Oct 18, 2013
- Messages
- 1
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.
<tbody>
</tbody>
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.
| Annual Personal Days | 2 | ||||||||||
| Employees automatcially receive 2 personal days on each anniversary date. | |||||||||||
| Years Employed | Days | Hours | Accrual Rate | ||||||||
| PTO Accrual Rates | 1st Year | 10 | 80 | 1.53846 | |||||||
| 2nd and 3rd | 15 | 120 | 2.30769 | ||||||||
| 4th and 5th | 18 | 144 | 2.76923 | ||||||||
| 6th+ | 21 | 168 | 3.23077 | ||||||||
| § Employees are eligible for Holiday Pay upon hire. | |||||||||||
| § New employees begin accrual of PTO from day one but use is not permitted until the 90 probation period is completed. | |||||||||||
| § Personal days are not available for use until 90 days of service is completed. | |||||||||||
| § Employees can carry over a maximum of 60 hours PTO from the previous year. | |||||||||||
| § Unused PTO is payable at termination of employment up to 80 hours accrued. | |||||||||||
| Employee | Hire | Todays | Years | Hours | PTO Accrued | PTO Hours | PTO Days | ||||
| Name | Date | Date | Worked | Used | To Date | Available | Available | ||||
| 12/13/2013 | 10/18/2013 | 0 | 8 | 1) *See Notation Below | #VALUE! | #VALUE! | |||||
| 8/25/2013 | 10/18/2013 | 0 | 32 | -32 | -4 | ||||||
| 9/12/2013 | 10/18/2013 | 0 | 16 | -16 | -2 | ||||||
| 10/10/2005 | 10/18/2013 | 8 | 24 | -24 | -3 | ||||||
| 8/22/2012 | 10/18/2013 | 1 | 20 | -20 | -2.5 | ||||||
| 11/19/2009 | 10/18/2013 | 4 | 16 | -16 | -2 | ||||||
| 7/14/1997 | 10/18/2013 | 16 | 12 | -12 | -1.5 | ||||||
| 8/5/2013 | 10/18/2013 | 0 | 48 | -48 | -6 | ||||||
| 6/13/2012 | 10/18/2013 | 1 | 80 | -80 | -10 | ||||||
| 9/27/2010 | 10/18/2013 | 3 | 60 | -60 | -7.5 | ||||||
| 11/7/2012 | 10/18/2013 | 1 | 64 | -64 | -8 | ||||||
| 6/28/2012 | 10/18/2013 | 1 | 24 | -24 | -3 | ||||||
| 4/2/2013 | 10/18/2013 | 0 | 8 | -8 | -1 | ||||||
| 12/5/2012 | 10/18/2013 | 1 | 0 | 0 | 0 | ||||||
| 12/23/2002 | 10/18/2013 | 11 | 80 | -80 | -10 | ||||||
| 6/21/2011 | 10/18/2013 | 2 | 120 | -120 | -15 | ||||||
| 6/24/2013 | 10/18/2013 | 0 | 12 | -12 | -1.5 | ||||||
| 1) *This calculation is as follows: (Number of weeks since last anniversary x accrual rate) + 16 hours of annual personal days. | |||||||||||
<tbody>
</tbody>
Last edited:
