PTO Accrual Tracker

mr shifty · Mar 21, 2019

I am trying to calculate PTO for my company of 600+ employees. I have used a few of the formulas posted here over the years, but can't seem to get it work per our policy

Example:
If an employee has a hire date of 6/3/18, from 12/16/18 through today would equal 6 pay periods at the <1 year rate equaling 22.158 hours (6*3.693). The issue is when the employee would get to their 1 year anniversary which would be the 12th pay period (6/2/19). At 12 pay periods, the employee will have 44.316 (12*3.693) hours and then on the 13th pay period start accruing at the 4.309 rate. Is there a formula to show this? Most formulas I have used change the total balance from 44.316 to 51.696 (12*4.308).

Accrued biweekly starting 12/16/18:
0-1 years = 3.693 PTO hours/biweekly
1-5 years = 4.308 PTO hours/biweekly
5+ years = 5.539 PTO hours/biweekly

So far I have the following:
C1 start of the pay period 12/16/2018
D1 Today
E1 =INT((D1-C1)/14)
A3 Name
B3 Hire Date
C3 show the accrued balance

Thanks!

Jeffrey Mahoney · Mar 22, 2019

I've been a payroll professional for a total of about 13 years. I have questions.

Does your company limit the number of hours an employee can roll over into a new fiscal or calendar year? This would need to a variable in the calculation.

Are you not calculating the rate of PTO based on the number of hours a person works up to a maximum of 80 hours biweekly? Most of the company policies I've seen prorated the PTO accrual based on the percentage of actual hours worked up to a maximum of 100% or 40 hours per week. Each pay period should be a stand-alone calculation, not a combined calculation for a number of pay periods. First year calculation for each period: 3.693 * (number of hours worked / number of hours in period) or 3.639 * (79 / 80) = 3.6468. This would then get added to all other accrual from previous periods and used PTO would be subtracted.

Should the rate of accrual be based on the date at which the pay period started? Meaning, are you going to calculate the accrual differently if the first half of the pay period is in the first year and second half of the pay period is in the second year? You would then need to calculate the number of potential work hours there was in each accrual year.

Jeff

mr shifty · Mar 22, 2019

Jeffrey Mahoney said:
I've been a payroll professional for a total of about 13 years. I have questions.

Does your company limit the number of hours an employee can roll over into a new fiscal or calendar year? This would need to a variable in the calculation.

Are you not calculating the rate of PTO based on the number of hours a person works up to a maximum of 80 hours biweekly? Most of the company policies I've seen prorated the PTO accrual based on the percentage of actual hours worked up to a maximum of 100% or 40 hours per week. Each pay period should be a stand-alone calculation, not a combined calculation for a number of pay periods. First year calculation for each period: 3.693 * (number of hours worked / number of hours in period) or 3.639 * (79 / 80) = 3.6468. This would then get added to all other accrual from previous periods and used PTO would be subtracted.

Should the rate of accrual be based on the date at which the pay period started? Meaning, are you going to calculate the accrual differently if the first half of the pay period is in the first year and second half of the pay period is in the second year? You would then need to calculate the number of potential work hours there was in each accrual year.

Jeff

Roll over is not capped, there is however a cap on how many hours you can have banked (1.5 accrual for the year). Once the cap is hit, you stop accruing until PTO is used.

We do not calculate based on hours work, its a flat rate for all.

New rate would be calculated on the next pay cycle using the example you provided.

Jeffrey Mahoney · Mar 22, 2019

I'm working on a table and formula. When you say biweekly, you mean every two weeks?

So, if a person doesn't work at all for a period, nor takes any PTO they still accrue PTO?

Is 1.5 Accrual for the year based on this table?

Excel 2013/2016

A

B

C

D

1

Years

Biweekly Accrual

Annual PTO

Max PTO

2

0

3.693

96.018

144.027

3

1

4.308

112.008

168.012

4

5

5.539

144.014

216.021

5

99

5.539

144.014

216.021

PTO

Cell Formulas
Range	Formula
C2	=B2*26

mr shifty · Mar 22, 2019

Jeffrey Mahoney said:
I'm working on a table and formula. When you say biweekly, you mean every two weeks?

So, if a person doesn't work at all for a period, nor takes any PTO they still accrue PTO?

Is 1.5 Accrual for the year based on this table?
Excel 2013/2016

A B C D
1 Years Biweekly Accrual Annual PTO Max PTO
2 0 3.693 96.018 144.027
3 1 4.308 112.008 168.012
4 5 5.539 144.014 216.021
5 99 5.539 144.014 216.021

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
PTO

Worksheet Formulas

Cell Formula
C2 =B2*26

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>

Yes, biweekly = every 2 weeks.

Yes

Yes

Jeffrey Mahoney · Mar 22, 2019

Ok, this will probably need some tweaking. But this is a good start.

You said you wanted to split the pay period PTO if the period crossed over a year break. The formulas in G2:AG7 are doing just that.

Red is a test if the pay period date is older than today
Blue is a test to see if the period has crossed over an Accrual years (ie, over 1 year, over 5 years).
Green is calculating the percent of days that fall into the lower Accrual years and the percent of days that fall into the upper Accrual years and multiply the period accrual hours for each
Purple is returning the Accrual hours if the period doesn't cross over an Accrual year break

=IF(I$2<=NOW(),IF(VLOOKUP(YEARFRAC($B4,I$2),PTOAccrual_tbl,1,TRUE)>VLOOKUP(YEARFRAC($B4,I$2-14),PTOAccrual_tbl,1,TRUE),VLOOKUP(YEARFRAC($B4,I$2-14),PTOAccrual_tbl,2,TRUE)*((EDATE($B4,INT(YEARFRAC($B4,I$2))*12)-(I$2-14))/14)+VLOOKUP(YEARFRAC($B4,I$2),PTOAccrual_tbl,2,TRUE)*((I$2-EDATE($B4,INT(YEARFRAC($B4,I$2))*12))/14),VLOOKUP(YEARFRAC($B4,I$2),PTOAccrual_tbl,2,TRUE)),0)

Cell I4 shows a calculation where the period crosses over an Accrual year break

Excel 2013/2016

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG
1	2019		12/31/2018				PTO Accrual for Period Ending								PTO Accrual for Period Ending								PTO Accrual for Period Ending								PTO Accrual for Period Ending
2		Period Start:	Prior	Prior		Current	1/14/2019	1/28/2019	2/11/2019	2/25/2019	3/11/2019	3/25/2019	4/8/2019	4/22/2019	5/6/2019	5/20/2019	6/3/2019	6/17/2019	7/1/2019	7/15/2019	7/29/2019	8/12/2019	8/26/2019	9/9/2019	9/23/2019	10/7/2019	10/21/2019	11/4/2019	11/18/2019	12/2/2019	12/16/2019	12/30/2019	1/13/2020
3	Name	Hire Date	Accrued Years	PTO Balance	PTO Taken	PTO Balance	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27
4	Bob White	2/1/2018	0.92	0.00	8.00	12.13	3.693	3.693	4.132	4.308	4.308	0.000	0.000	0.000	0.000	0.000	0.000	0.000	0.000	0.000	0.000	0.000	0.000	0.000	0.000	0.000	0.000	0.000	0.000	0.000	0.000	0.000	0.000
5	Jan Smith	5/1/2017	1.67	168.00	20.00	168.01	4.308	4.308	4.308	4.308	4.308	0.000	0.000	0.000	0.000	0.000	0.000	0.000	0.000	0.000	0.000	0.000	0.000	0.000	0.000	0.000	0.000	0.000	0.000	0.000	0.000	0.000	0.000
6	George Jones	9/12/1989	29.30	220.00	20.00	216.02	5.539	5.539	5.539	5.539	5.539	0.000	0.000	0.000	0.000	0.000	0.000	0.000	0.000	0.000	0.000	0.000	0.000	0.000	0.000	0.000	0.000	0.000	0.000	0.000	0.000	0.000	0.000
7	Janis Joplin	12/14/2014	4.05	40.00	20.00	41.54	4.308	4.308	4.308	4.308	4.308	0.000	0.000	0.000	0.000	0.000	0.000	0.000	0.000	0.000	0.000	0.000	0.000	0.000	0.000	0.000	0.000	0.000	0.000	0.000	0.000	0.000	0.000

PTO Calc

Worksheet Formulas

Cell	Formula
G2	=FirstDate+14
H2	=G2+14
I2	=H2+14
C4	=YEARFRAC(B4,FirstDate)
F4	=MIN(D4+SUM(G4:AG4)-E4,VLOOKUP(MIN(YEARFRAC(B4,NOW()),YEARFRAC(B4,DATE(CurYear,12,31))),PTOAccrual_tbl,4,TRUE))
G4	=IF(G$2<=NOW(),IF(VLOOKUP(YEARFRAC($B4,G$2),PTOAccrual_tbl,1,TRUE)>VLOOKUP(YEARFRAC($B4,G$2-14),PTOAccrual_tbl,1,TRUE),VLOOKUP(YEARFRAC($B4,G$2-14),PTOAccrual_tbl,2,TRUE)((EDATE($B4,INT(YEARFRAC($B4,G$2))12)-(G$2-14))/14)+VLOOKUP(YEARFRAC($B4,G$2),PTOAccrual_tbl,2,TRUE)((G$2-EDATE($B4,INT(YEARFRAC($B4,G$2))12))/14),VLOOKUP(YEARFRAC($B4,G$2),PTOAccrual_tbl,2,TRUE)),0)
H4	=IF(H$2<=NOW(),IF(VLOOKUP(YEARFRAC($B4,H$2),PTOAccrual_tbl,1,TRUE)>VLOOKUP(YEARFRAC($B4,H$2-14),PTOAccrual_tbl,1,TRUE),VLOOKUP(YEARFRAC($B4,H$2-14),PTOAccrual_tbl,2,TRUE)((EDATE($B4,INT(YEARFRAC($B4,H$2))12)-(H$2-14))/14)+VLOOKUP(YEARFRAC($B4,H$2),PTOAccrual_tbl,2,TRUE)((H$2-EDATE($B4,INT(YEARFRAC($B4,H$2))12))/14),VLOOKUP(YEARFRAC($B4,H$2),PTOAccrual_tbl,2,TRUE)),0)
I4	=IF(I$2<=NOW(),IF(VLOOKUP(YEARFRAC($B4,I$2),PTOAccrual_tbl,1,TRUE)>VLOOKUP(YEARFRAC($B4,I$2-14),PTOAccrual_tbl,1,TRUE),VLOOKUP(YEARFRAC($B4,I$2-14),PTOAccrual_tbl,2,TRUE)((EDATE($B4,INT(YEARFRAC($B4,I$2))12)-(I$2-14))/14)+VLOOKUP(YEARFRAC($B4,I$2),PTOAccrual_tbl,2,TRUE)((I$2-EDATE($B4,INT(YEARFRAC($B4,I$2))12))/14),VLOOKUP(YEARFRAC($B4,I$2),PTOAccrual_tbl,2,TRUE)),0)

Workbook Defined Names

Name	Refers To
CurYear	='PTO Calc'!$A$1
FirstDate	='PTO Calc'!$C$1
PTOAccrual_tbl	=PTO!$A$2:$D$5

mr shifty · Mar 26, 2019

Thank you! I will give this a go and let you know if I have any issues.

mr shifty · Mar 26, 2019

I used the exact info and I'm getting #N/A errors for the formulas in F4-I4

Jeffrey Mahoney · Mar 27, 2019

Do you have named ranges for CurYear, FirstDate, and PTOAccrual_tbl? CurYear should be the current year, as in 2019. FirstDate should be the date for the beginning of the first pay period. The PTOAccrual_tbl should look like the one in Post 5.

Please confirm.

mr shifty · Mar 27, 2019

Jeffrey Mahoney said:
Do you have named ranges for CurYear, FirstDate, and PTOAccrual_tbl? CurYear should be the current year, as in 2019. FirstDate should be the date for the beginning of the first pay period. The PTOAccrual_tbl should look like the one in Post 5.

Please confirm.

Ahh I missed that, it works now. The formulas don't work past column M4

PTO Accrual Tracker

mr shifty

New Member

Excel Facts

Jeffrey Mahoney

Well-known Member

mr shifty

New Member

Jeffrey Mahoney

Well-known Member

mr shifty

New Member

Jeffrey Mahoney

Well-known Member

mr shifty

New Member

mr shifty

New Member

Jeffrey Mahoney

Well-known Member

mr shifty

New Member

Similar threads

Forum statistics

Share this page

PTO Accrual Tracker

New Member

Excel Facts

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

New Member

New Member

Well-known Member

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock