Dynamic PTO Accrual

[ryland00]

Hello,

I am looking to see if anyone has a nice formula for a problem I am running into with my PTO accrual sheet. Where I am having issues, is when the anniversary date of the employee comes and the accrual would change.

Given the following table :

0-4 years : 5 hours
>4-9 years : 7 hours
>9-14 years : 9 hours
>14 years : 11 hours

The problem I am having is, for instance, if someone started on May 5, 2009. Up until May 5, the employee is accruing 7 hours, but after May 5, the employee should start accruing 9 hours. What formula can I use to calculate this based on a "today" formula?

I am currently using a combination of these formulas.

=IF(E2="",0,(TODAY()-E2)/365) <-- To calc the years of service
=IF(AND(F2>0,F2<4),5,IF(AND(F2>4,F2<9),7,IF(AND(F2>9,F2<14),9,IF(F2>14,11,0)))) <-- To calc the accrual amount
=IFERROR(VLOOKUP(B10,Key!C:G,5,0)*(LOOKUP(TODAY(),Key!J:J,Key!K:K) <--- First vlookup brings in the accrual amount by person, and the lookup brings in the number of weeks based on what I have posted below.


[TABLE="width: 228"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]2018 Pay Dates[/TD]
[TD]#[/TD]
[/TR]
[TR]
[TD="align: right"]12-Jan[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]26-Jan[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]9-Feb[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]23-Feb[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]9-Mar[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]23-Mar[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: right"]6-Apr[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD="align: right"]20-Apr[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="align: right"]4-May[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD="align: right"]18-May[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="align: right"]1-Jun[/TD]
[TD="align: right"]11[/TD]
[/TR]
[TR]
[TD="align: right"]15-Jun[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD="align: right"]29-Jun[/TD]
[TD="align: right"]13[/TD]
[/TR]
[TR]
[TD="align: right"]13-Jul[/TD]
[TD="align: right"]14[/TD]
[/TR]
[TR]
[TD="align: right"]27-Jul[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD="align: right"]10-Aug[/TD]
[TD="align: right"]16[/TD]
[/TR]
[TR]
[TD="align: right"]24-Aug[/TD]
[TD="align: right"]17[/TD]
[/TR]
[TR]
[TD="align: right"]7-Sep[/TD]
[TD="align: right"]18[/TD]
[/TR]
[TR]
[TD="align: right"]21-Sep[/TD]
[TD="align: right"]19[/TD]
[/TR]
[TR]
[TD="align: right"]5-Oct[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD="align: right"]19-Oct[/TD]
[TD="align: right"]21[/TD]
[/TR]
[TR]
[TD="align: right"]2-Nov[/TD]
[TD="align: right"]22[/TD]
[/TR]
[TR]
[TD="align: right"]16-Nov[/TD]
[TD="align: right"]23[/TD]
[/TR]
[TR]
[TD="align: right"]30-Nov[/TD]
[TD="align: right"]24[/TD]
[/TR]
[TR]
[TD="align: right"]14-Dec[/TD]
[TD="align: right"]25[/TD]
[/TR]
[TR]
[TD="align: right"]28-Dec[/TD]
[TD="align: right"]26[/TD]
[/TR]
</tbody>[/TABLE]

 

[mamady]

Hi ryland00

Can you please attach a sample sheet to better understand your current formulas and output cells?

Best Regards