Help

Calculating Annual PaidTimeOff based on StartDate, WorkingDays, Holidays and Company PTO Rules

Topic Labels: Formulas
1143 1
cancel
Showing results for 
Search instead for 
Did you mean: 
molasscoe
4 - Data Explorer
4 - Data Explorer

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

1 Reply 1

Maybe I just haven’t mulled this over enough, but the mulling that I’ve done so far leads me to think that if this could be done via formulas, it would be a headache. Airtable’s formula system is pretty cool, but not quite robust enough for the kind of logic that you describe. I immediately think about scripting when something this complex comes along.

Are you willing to entertain the idea of pulling this off via a script? If so, are you interested in learning how to write the script yourself, or would you prefer to hire a developer to write it for you?