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.

2018 Pay Dates	#
12-Jan	1
26-Jan	2
9-Feb	3
23-Feb	4
9-Mar	5
23-Mar	6
6-Apr	7
20-Apr	8
4-May	9
18-May	10
1-Jun	11
15-Jun	12
29-Jun	13
13-Jul	14
27-Jul	15
10-Aug	16
24-Aug	17
7-Sep	18
21-Sep	19
5-Oct	20
19-Oct	21
2-Nov	22
16-Nov	23
30-Nov	24
14-Dec	25
28-Dec	26

<colgroup><col><col></colgroup><tbody>
</tbody>

 

[mamady]

Hi ryland00

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

Best Regards