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.
<tbody>
</tbody>
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.
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 |
<tbody>
</tbody>
Last edited: