# PTO Accrual Formulas

#### merfl96

##### New Member
Hi, longtime lurker and first-time poster. I am working on creating a new PTO accrual sheet for my company and I need some help. I have looked through many other posts but I did not see anything that lined up more with what I am in need of.

Employees accrue PTO on a monthly basis and there is no rollover, and it is given at the start of each month (even though they haven't 'earned' it), meaning on Jan 1 they started fresh with x days or more depending on their accrual rate below.

The accrual rates at the company are as follows:

0.83 days a month for service time under 5 years

1.25 days a month for service times of at least 5 years but less than 15 years

1.66 days a month for service times of 15 years+

Currently I have a cell to calculate service time from their start date using the TODAY function. After that I have a cell with their accrual rate based on years of service and I am currently using this:

=IF(E2<5, ".83", IF(AND(E2>=5,E2<15), "1.25", IF(E2>=15, "1.66")))

Where E2 has the years of service.

I then have another cell to show their earned PTO. In this cell I was using the current month as the multiplier using *MONTH(TODAY())

I realized that if someone reaches a service milestone in the middle of the year my current formulas would multiply all previous earned days by their new accrual rate which would be a big boost to the employee. Additionally, another issue with what I have is that new hires would be getting months of vacation they were not here for. You cannot start earning until 90 days from your start date.

I would like to have a formula(s) that can automatically calculate PTO throughout the year, but allow for the service milestones that change accrual rates to automatically change without affecting previously earned days. If just that can be done and not the new hires, it would be okay to manually track their vacation time separately.

Any help or ideas would be greatly appreciated!

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

#### Dave Patton

##### Well-known Member
Book1
ABCDEFGHIJ
2NameStartService Months01-Jan-2001-Feb-2000.83
3A01-Jan-101201.251.25601.25
4B01-Jul-18180.830.831801.66
5C01-Jan-992521.661.66
6D01-Nov-1920.000.83
7
8A01-Jan-101201.251.25
9B01-Jul-18180.830.83
10C01-Jan-992521.661.66
11D01-Nov-1920.000.83
9a
Cell Formulas
RangeFormula
E3, E8E3=DATEDIF(D3-1,F\$2,"m")
F3:G3F3=(F\$2-\$D3>90)*LOOKUP(\$E3,\$I\$2:\$J\$4)
I4I4=15*12
F8:G8F8=(F\$2-\$D8>90)*LOOKUP(\$E8,{0,0.83;60,1.25;180,1.66})

You can format January etc as month. This post should show "January"
The second group is the same but they do not use the external lookup table.

#### merfl96

##### New Member
Thank you, Dave, for the quick reply!
I am using what you created, but I am noticing that it is still affecting previous time off. For example, I created an employee start date of 1/1/2015 as well as added in the month of 12/2019. When I did this it is showing that he accrued 1.25 days in 12/2019 when it should have been 0.83, and the 1.25 should have started only in 1/2020.
Any ideas for how to fix this?

#### Dave Patton

##### Well-known Member
Book1
ABCDEFG
1
2NameStart01-Dec-1901-Jan-2001-Feb-20
3A01-Jan-150.831.251.25
9a
Cell Formulas
RangeFormula
E3:G3E3=(E\$2-\$D3>90)*LOOKUP(DATEDIF(\$D3-1,E\$2,"m"),\$I\$2:\$J\$4)

#### merfl96

##### New Member
Thank you for the updated formula! Everything works and I truly appreciate your help!

#### Dave Patton

##### Well-known Member
Thanks for the feedback. Dave

Replies
7
Views
779
Replies
0
Views
163
Replies
1
Views
338
Replies
1
Views
548
Replies
6
Views
614

1,129,993
Messages
5,639,423
Members
417,089
Latest member
jonstr101

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

### Which adblocker are you using?

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

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