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