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

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

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
725
Replies
0
Views
144
Replies
1
Views
313
Replies
1
Views
507
Replies
6
Views
582

1,127,529
Messages
5,625,351
Members
416,096
Latest member
forevans

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.

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