Dynamic PTO Accrual

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.

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

<colgroup><col><col></colgroup><tbody>
</tbody>
 
Last edited:

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

mamady

Board Regular
Joined
Sep 23, 2011
Messages
198
Office Version
  1. 365
Platform
  1. Windows
Hi ryland00

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

Best Regards
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,162
Messages
5,594,615
Members
413,917
Latest member
devansh02

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
Top