Dynamic Formula needed

[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.

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 because they are in their second milestone, but after May 5, 2018, the employee should start accruing 9 hours. So, the employee should accrue 7 hours from Jan 1, 2018 to May 5, 2018, but then beginning May 6, 2018, the employee should accrue 9 hours going forward.

Given the following table :

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


I am currently using a combination of these formulas.

F2=IF(E2="",0,(TODAY()-E2)/365) <--E2 = The date that the person started: To calc the years of service
G2=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
H2=IFERROR(VLOOKUP(M2,C:G,5,0)*(LOOKUP(TODAY(),J:J,K:K) <--- First vlookup brings in the accrual amount by person (M2 is the name of the employee, and C is a list of employees, G is the accrual amount), and the lookup brings in the number of weeks based on what I have posted below. 2018 Pay dates is column J and # is column K. Basically this calculates accrual * the number of weeks.

[TABLE="class: cms_table, width: 228"]
<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]