Hi all! Thank you so much for helping with this math! I keep trying but short on solutions in how I’m thinking about this.
I am attempting to write a formula that will assign Paid Time Off (PTO) to an Employee based on Start Date and consuming our Business Rules for assigning PTO.
We have a Field labeled ‘Employee Type’ with values Hourly or Salary. Salary returns the result “Open PTO” and Hourly needs to do the math.
Hourly PTO Rules
Year 1 = 80 Hours PTO
Year 2 and 3 120 Hours PTO
Year 4 and beyond 160 Hours PTO
Optional Override Field {PTO Override} - if a value is entered here use it and ignore the Yearly rules above.
Ideally when an ‘Hourly’ employee ‘Starts’ they will get Annual PTO prorated up to 12/31/YEARNOW(). And the balance of their first 80 hours commencing on or before the {StartDate}. Then the pro-rated amount of Year 2 (120 Hours) up to 12/31/YEARNOW() +1.
In other words, an employees Annual (1/1-12/31) PTO amount will be the combination of the remaining amount from years of service + the level the employee WILL BE AT upon their annual {StartDate} anniversary.
We provide lump sum (upfront) PTO and do not accrue it based on hours worked. So each January first (1//1/YEARNOW() an hourly employee should receive their TOTAL allotment of PTO based on Years with the Company (or based upon the {PTO Override} field.
Does that make sense? For example, if a new employee {StartDate} = 9/12/2021… then they do not fall into the “Year 2 - 120 PTO Hours” level until 9/12/2022. Therefore for 2022 I need to assign a Partial allotment of 80 Hours for the period 1/1/2022-9/11/2022 then assign a partial allotment of 120 hours from 9/12/2022-12/31/2022.
I think I can build this in a manner that requires some manual editing and updating dates each year (and with many columns)… However, my goal is to achieve this without needing to update dates in formulas each year. Thanks so much!!!
