Dynamic Formula needed

ryland00

Board Regular
Joined
Jun 13, 2014
Messages
76
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.

2018 Pay Dates#
12-Jan1
26-Jan2
9-Feb3
23-Feb4
9-Mar5
23-Mar6
6-Apr7
20-Apr8
4-May9
18-May10
1-Jun11
15-Jun12
29-Jun13
13-Jul14
27-Jul15
10-Aug16
24-Aug17
7-Sep18
21-Sep19
5-Oct20
19-Oct21
2-Nov22
16-Nov23
30-Nov24
14-Dec25
28-Dec26

<tbody>
</tbody>
 
Last edited:

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Forum statistics

Threads
1,213,543
Messages
6,114,243
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top