PTO Accrual Tracker

[mr shifty]

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]

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]

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]

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]

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
Years	Biweekly Accrual	Annual PTO	Max PTO

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3.693[/TD]
[TD="align: right"]96.018[/TD]
[TD="align: right"]144.027[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4.308[/TD]
[TD="align: right"]112.008[/TD]
[TD="align: right"]168.012[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5.539[/TD]
[TD="align: right"]144.014[/TD]
[TD="align: right"]216.021[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]99[/TD]
[TD="align: right"]5.539[/TD]
[TD="align: right"]144.014[/TD]
[TD="align: right"]216.021[/TD]

</tbody>

PTO​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C2[/TH]
[TD="align: left"]=B2*26[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Yes, biweekly = every 2 weeks.

Yes

Yes

 

[Jeffrey Mahoney]

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
PTO Accrual for Period Ending	PTO Accrual for Period Ending	PTO Accrual for Period Ending	PTO Accrual for Period Ending
Name	Hire Date	PTO Balance	PTO Taken	PTO Balance
Bob White
Jan Smith
George Jones
Janis Joplin

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]2019[/TD]
[TD="align: right"][/TD]
[TD="align: center"]12/31/2018[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"]Period Start:[/TD]
[TD="align: center"]Prior[/TD]
[TD="align: center"]Prior[/TD]
[TD="align: right"][/TD]
[TD="align: right"]Current[/TD]
[TD="align: right"]1/14/2019[/TD]
[TD="align: center"]1/28/2019[/TD]
[TD="align: center"]2/11/2019[/TD]
[TD="align: center"]2/25/2019[/TD]
[TD="align: center"]3/11/2019[/TD]
[TD="align: center"]3/25/2019[/TD]
[TD="align: center"]4/8/2019[/TD]
[TD="align: center"]4/22/2019[/TD]
[TD="align: center"]5/6/2019[/TD]
[TD="align: center"]5/20/2019[/TD]
[TD="align: center"]6/3/2019[/TD]
[TD="align: center"]6/17/2019[/TD]
[TD="align: center"]7/1/2019[/TD]
[TD="align: center"]7/15/2019[/TD]
[TD="align: center"]7/29/2019[/TD]
[TD="align: center"]8/12/2019[/TD]
[TD="align: center"]8/26/2019[/TD]
[TD="align: center"]9/9/2019[/TD]
[TD="align: center"]9/23/2019[/TD]
[TD="align: center"]10/7/2019[/TD]
[TD="align: center"]10/21/2019[/TD]
[TD="align: center"]11/4/2019[/TD]
[TD="align: center"]11/18/2019[/TD]
[TD="align: center"]12/2/2019[/TD]
[TD="align: center"]12/16/2019[/TD]
[TD="align: center"]12/30/2019[/TD]
[TD="align: center"]1/13/2020[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]Accrued Years[/TD]

[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]13[/TD]
[TD="align: center"]14[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]16[/TD]
[TD="align: center"]17[/TD]
[TD="align: center"]18[/TD]
[TD="align: center"]19[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]21[/TD]
[TD="align: center"]22[/TD]
[TD="align: center"]23[/TD]
[TD="align: center"]24[/TD]
[TD="align: center"]25[/TD]
[TD="align: center"]26[/TD]
[TD="align: center"]27[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]2/1/2018[/TD]
[TD="align: right"]0.92[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]8.00[/TD]
[TD="align: right"]12.13[/TD]
[TD="align: right"]3.693[/TD]
[TD="align: right"]3.693[/TD]
[TD="align: right"]4.132[/TD]
[TD="align: right"]4.308[/TD]
[TD="align: right"]4.308[/TD]
[TD="align: right"]0.000[/TD]
[TD="align: right"]0.000[/TD]
[TD="align: right"]0.000[/TD]
[TD="align: right"]0.000[/TD]
[TD="align: right"]0.000[/TD]
[TD="align: right"]0.000[/TD]
[TD="align: right"]0.000[/TD]
[TD="align: right"]0.000[/TD]
[TD="align: right"]0.000[/TD]
[TD="align: right"]0.000[/TD]
[TD="align: right"]0.000[/TD]
[TD="align: right"]0.000[/TD]
[TD="align: right"]0.000[/TD]
[TD="align: right"]0.000[/TD]
[TD="align: right"]0.000[/TD]
[TD="align: right"]0.000[/TD]
[TD="align: right"]0.000[/TD]
[TD="align: right"]0.000[/TD]
[TD="align: right"]0.000[/TD]
[TD="align: right"]0.000[/TD]
[TD="align: right"]0.000[/TD]
[TD="align: right"]0.000[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]5/1/2017[/TD]
[TD="align: right"]1.67[/TD]
[TD="align: right"]168.00[/TD]
[TD="align: right"]20.00[/TD]
[TD="align: right"]168.01[/TD]
[TD="align: right"]4.308[/TD]
[TD="align: right"]4.308[/TD]
[TD="align: right"]4.308[/TD]
[TD="align: right"]4.308[/TD]
[TD="align: right"]4.308[/TD]
[TD="align: right"]0.000[/TD]
[TD="align: right"]0.000[/TD]
[TD="align: right"]0.000[/TD]
[TD="align: right"]0.000[/TD]
[TD="align: right"]0.000[/TD]
[TD="align: right"]0.000[/TD]
[TD="align: right"]0.000[/TD]
[TD="align: right"]0.000[/TD]
[TD="align: right"]0.000[/TD]
[TD="align: right"]0.000[/TD]
[TD="align: right"]0.000[/TD]
[TD="align: right"]0.000[/TD]
[TD="align: right"]0.000[/TD]
[TD="align: right"]0.000[/TD]
[TD="align: right"]0.000[/TD]
[TD="align: right"]0.000[/TD]
[TD="align: right"]0.000[/TD]
[TD="align: right"]0.000[/TD]
[TD="align: right"]0.000[/TD]
[TD="align: right"]0.000[/TD]
[TD="align: right"]0.000[/TD]
[TD="align: right"]0.000[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]9/12/1989[/TD]
[TD="align: right"]29.30[/TD]
[TD="align: right"]220.00[/TD]
[TD="align: right"]20.00[/TD]
[TD="align: right"]216.02[/TD]
[TD="align: right"]5.539[/TD]
[TD="align: right"]5.539[/TD]
[TD="align: right"]5.539[/TD]
[TD="align: right"]5.539[/TD]
[TD="align: right"]5.539[/TD]
[TD="align: right"]0.000[/TD]
[TD="align: right"]0.000[/TD]
[TD="align: right"]0.000[/TD]
[TD="align: right"]0.000[/TD]
[TD="align: right"]0.000[/TD]
[TD="align: right"]0.000[/TD]
[TD="align: right"]0.000[/TD]
[TD="align: right"]0.000[/TD]
[TD="align: right"]0.000[/TD]
[TD="align: right"]0.000[/TD]
[TD="align: right"]0.000[/TD]
[TD="align: right"]0.000[/TD]
[TD="align: right"]0.000[/TD]
[TD="align: right"]0.000[/TD]
[TD="align: right"]0.000[/TD]
[TD="align: right"]0.000[/TD]
[TD="align: right"]0.000[/TD]
[TD="align: right"]0.000[/TD]
[TD="align: right"]0.000[/TD]
[TD="align: right"]0.000[/TD]
[TD="align: right"]0.000[/TD]
[TD="align: right"]0.000[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]12/14/2014[/TD]
[TD="align: right"]4.05[/TD]
[TD="align: right"]40.00[/TD]
[TD="align: right"]20.00[/TD]
[TD="align: right"]41.54[/TD]
[TD="align: right"]4.308[/TD]
[TD="align: right"]4.308[/TD]
[TD="align: right"]4.308[/TD]
[TD="align: right"]4.308[/TD]
[TD="align: right"]4.308[/TD]
[TD="align: right"]0.000[/TD]
[TD="align: right"]0.000[/TD]
[TD="align: right"]0.000[/TD]
[TD="align: right"]0.000[/TD]
[TD="align: right"]0.000[/TD]
[TD="align: right"]0.000[/TD]
[TD="align: right"]0.000[/TD]
[TD="align: right"]0.000[/TD]
[TD="align: right"]0.000[/TD]
[TD="align: right"]0.000[/TD]
[TD="align: right"]0.000[/TD]
[TD="align: right"]0.000[/TD]
[TD="align: right"]0.000[/TD]
[TD="align: right"]0.000[/TD]
[TD="align: right"]0.000[/TD]
[TD="align: right"]0.000[/TD]
[TD="align: right"]0.000[/TD]
[TD="align: right"]0.000[/TD]
[TD="align: right"]0.000[/TD]
[TD="align: right"]0.000[/TD]
[TD="align: right"]0.000[/TD]
[TD="align: right"]0.000[/TD]

</tbody>

PTO Calc​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G2[/TH]
[TD="align: left"]=FirstDate+14[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H2[/TH]
[TD="align: left"]=G2+14[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I2[/TH]
[TD="align: left"]=H2+14[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C4[/TH]
[TD="align: left"]=YEARFRAC(B4,FirstDate)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F4[/TH]
[TD="align: left"]=MIN(D4+SUM(G4:AG4)-E4,VLOOKUP(MIN(YEARFRAC(B4,NOW()),YEARFRAC(B4,DATE(CurYear,12,31))),PTOAccrual_tbl,4,TRUE))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G4[/TH]
[TD="align: left"]=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)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H4[/TH]
[TD="align: left"]=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)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I4[/TH]
[TD="align: left"]=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)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Workbook Defined Names[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Name[/TH]
[TH="align: left"]Refers To[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]CurYear[/TH]
[TD="align: left"]='PTO Calc'!$A$1[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]FirstDate[/TH]
[TD="align: left"]='PTO Calc'!$C$1[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]PTOAccrual_tbl[/TH]
[TD="align: left"]=PTO!$A$2:$D$5[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[mr shifty]

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

 

[mr shifty]

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

 

[Jeffrey Mahoney]

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]

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