I'm trying to figure out some formulas to calculate how much PTO my employees have, and here are the rules for our PTO
PTO is figured by an hourly rate
PTO can be rolled over at the max amount of PTO available for the year
Once an employee has reached their max amount of PTO and taken all of their PTO for the year, they begin accruing for next year
Once an employee reaches their max amount of PTO, PTO stops accruing until they use some
An employee cannot use more than their allotted PTO for the year
I have a VLookup to figure out max PTO hours possible, and their accrual rate.
I enter their daily time, and that adds up to figure out how many PTO hours that they have earned
So, what I'm trying to calculate is total PTO hours, how many are available for this year, and how many they have earned towards their rollover
This is what I have, but the PTO Hours available for 2019 are not calculating correctly (Date of hire begins on row 8 and the first employee begins in Column C)
[TABLE="border: 0, cellpadding: 0, cellspacing: 0"]
[TD="width: 159, colspan: 2"]Date of Hire[/TD]
[TD="width: 94, colspan: 2"]4/11/1989[/TD]
[TD="width: 90, colspan: 2"]11/1/2011[/TD]
[TD="width: 90, colspan: 2"]4/1/1987[/TD]
[TD="width: 94, colspan: 2"]3/11/1996[/TD]
[TD="width: 90, colspan: 2"]9/9/1971[/TD]
[TD="width: 90, colspan: 2"]6/10/2019[/TD]
[TD="width: 159, colspan: 2"]Service Length[/TD]
[TD="width: 94, colspan: 2"]30[/TD]
[TD="width: 90, colspan: 2"]8[/TD]
[TD="width: 90, colspan: 2"]32[/TD]
[TD="width: 94, colspan: 2"]23[/TD]
[TD="width: 90, colspan: 2"]48[/TD]
[TD="width: 90, colspan: 2"]1[/TD]
[TD="width: 159, colspan: 2"]Union[/TD]
[TD="width: 94, colspan: 2"] [/TD]
[TD="width: 90, colspan: 2"] [/TD]
[TD="width: 90, colspan: 2"] [/TD]
[TD="width: 94, colspan: 2"] [/TD]
[TD="width: 90, colspan: 2"] [/TD]
[TD="width: 90, colspan: 2"] [/TD]
[TD="width: 159, colspan: 2"]Salaried[/TD]
[TD="width: 94, colspan: 2"] [/TD]
[TD="width: 90, colspan: 2"]x[/TD]
[TD="width: 90, colspan: 2"] [/TD]
[TD="width: 94, colspan: 2"] [/TD]
[TD="width: 90, colspan: 2"] [/TD]
[TD="width: 90, colspan: 2"] [/TD]
[TD="width: 86"] [/TD]
[TD="width: 73"] [/TD]
[TD="width: 94, colspan: 2"]A Plant[/TD]
[TD="width: 90, colspan: 2"]Sales[/TD]
[TD="width: 90, colspan: 2"]Shop[/TD]
[TD="width: 94, colspan: 2"]A Plant[/TD]
[TD="width: 90, colspan: 2"]Geode[/TD]
[TD="width: 90, colspan: 2"]A Plant[/TD]
[TD="colspan: 2"]Classification[/TD]
[TD="colspan: 2"] Field [/TD]
[TD="colspan: 2"] Office / Mgr [/TD]
[TD="colspan: 2"] Office / Mgr [/TD]
[TD="colspan: 2"] Field [/TD]
[TD="colspan: 2"] Field [/TD]
[TD="colspan: 2"] Field [/TD]
[TD="colspan: 2"] [/TD]
[TD="colspan: 2"] [/TD]
[TD="colspan: 2"] [/TD]
[TD="colspan: 2"] [/TD]
[TD="colspan: 2"] [/TD]
[TD="colspan: 2"] [/TD]
[TD="width: 159, colspan: 2"]Yearly Totals[/TD]
[TD="colspan: 2"] 990.50[/TD]
[TD="colspan: 2"] 1,136.00[/TD]
[TD="colspan: 2"] 1,139.50[/TD]
[TD="colspan: 2"] 1,445.25[/TD]
[TD="colspan: 2"] 1,025.75[/TD]
[TD="colspan: 2"] 211.00[/TD]
[TD="colspan: 2"]PTO Hrs Possible[/TD]
[TD="colspan: 2"]120[/TD]
[TD="colspan: 2"]160[/TD]
[TD="colspan: 2"]160[/TD]
[TD="colspan: 2"]120[/TD]
[TD="colspan: 2"]120[/TD]
[TD="colspan: 2"]56[/TD]
[TD="colspan: 2"]Accrual Rate[/TD]
[TD="colspan: 2"]0.075[/TD]
[TD="colspan: 2"]0.1[/TD]
[TD="colspan: 2"]0.1[/TD]
[TD="colspan: 2"]0.075[/TD]
[TD="colspan: 2"]0.075[/TD]
[TD="colspan: 2"]0.03[/TD]
[TD="colspan: 2"]PTO Hrs Earned[/TD]
[TD="colspan: 2"] 74.29[/TD]
[TD="colspan: 2"] 113.60[/TD]
[TD="colspan: 2"] 113.95[/TD]
[TD="colspan: 2"] 108.39[/TD]
[TD="colspan: 2"] 76.93[/TD]
[TD="colspan: 2"] 6.33[/TD]
[TD="colspan: 2"]PTO Hrs Carried Over[/TD]
[TD="colspan: 2"]4.33[/TD]
[TD="colspan: 2"]94.4[/TD]
[TD="colspan: 2"]55.02[/TD]
[TD="colspan: 2"]30.13[/TD]
[TD="colspan: 2"]115.99[/TD]
[TD="colspan: 2"]0[/TD]
[TD="width: 159, colspan: 2"]PTO Hrs Used[/TD]
[TD="colspan: 2"] 32.00[/TD]
[TD="colspan: 2"] 48.00[/TD]
[TD="colspan: 2"] 34.00[/TD]
[TD="colspan: 2"] 34.00[/TD]
[TD="colspan: 2"] 112.50[/TD]
[TD="colspan: 2"] - [/TD]
[TD="width: 159, colspan: 2"]PTO Hrs Scheduled[/TD]
[TD="colspan: 2"] - [/TD]
[TD="colspan: 2"] - [/TD]
[TD="colspan: 2"] - [/TD]
[TD="colspan: 2"] - [/TD]
[TD="colspan: 2"] - [/TD]
[TD="colspan: 2"] - [/TD]
[TD="width: 159, colspan: 2"]PTO Hrs available for 2019[/TD]
[TD="colspan: 2"] 78.62[/TD]
[TD="colspan: 2"] 112.00[/TD]
[TD="colspan: 2"] 126.00[/TD]
[TD="colspan: 2"] 86.00[/TD]
[TD="colspan: 2"] 7.50[/TD]
[TD="colspan: 2"] 6.33[/TD]
[TD="width: 159, colspan: 2"]Total PTO Hrs Available[/TD]
[TD="colspan: 2"] 46.62[/TD]
[TD="colspan: 2"] 160.00[/TD]
[TD="colspan: 2"] 134.97[/TD]
[TD="colspan: 2"] 104.52[/TD]
[TD="colspan: 2"] 80.42[/TD]
[TD="colspan: 2"] 6.33[/TD]
[TD="width: 159, colspan: 2"]PTO Hrs Earned for Rollover[/TD]
[TD="colspan: 2"] - [/TD]
[TD="colspan: 2"] 48.00[/TD]
[TD="colspan: 2"] - [/TD]
[TD="colspan: 2"] 18.52[/TD]
[TD="colspan: 2"] 72.92[/TD]
[TD="colspan: 2"] - [/TD]
[/TABLE]
[TABLE="border: 0, cellpadding: 0, cellspacing: 0"]
[TD="width: 319, colspan: 2"]PTO Hrs available for 2019[/TD]
[TD="width: 525, colspan: 2"]=IF(OR(C18>=C16,C18+C19>=C16),C16-C20,C18+C19)[/TD]
[TD="width: 319, colspan: 2"]Total PTO Hrs Available[/TD]
[TD="colspan: 2"]=MIN(C16,IF(SUM(C18,C19,-C20)=C16,C16,SUM(C18,C19,-C20)))[/TD]
[TD="width: 319, colspan: 2"]PTO Hrs Earned for Rollover[/TD]
[TD="colspan: 2"]=IF(OR(C16-C20<=0,C20+C18-C16<=0),0,(C23-C22))[/TD]
[/TABLE]
Thanks in advance for any help.