Coda paid time off (PTO) — 9.0
Fixing the PTO balance
In our blog on replacing holidays with sick leave, I noticed a potential problem while reviewing the balance calculations. For example, canceling a full day after booking a half-day off results in a net gain of half a day.
That needs a solid fix.
As a reminder, the ‘day’ value is either 0.5 for a half-day or 1 for a full day. The calculation behind it considers the employee’s choice and the first/last dates of the requested period, factoring in their work schedule.
However, isolating a single date disrupts this logic. The first/last date references in our formulas are relative, leading to incorrect calculations.
We can:
- Use a button to fix the values for the affected date range. Once printed, these values become independent of formulas and can be copied into a new row.
- Adapt our existing formula. We’ll retain the current logic for standard requests, but for cancellations, use the values associated with the canceled request. This is complex, as a single date can have multiple requests and cancellations.
Before we explore the solution, let’s check out the final result! Here’s a tricky example to show how powerful these buttons and formulas are. Imagine an employee books a holiday, even on non-working days. Then, they get sick during part of that holiday. We’ll cancel the overlapping holidays and add sick days.
But what if they accidentally request sick leave again for those same days? We’ll flag that as a double booking. And to top it off, let’s say they cancel some sick leave… even for a day they were never sick! Our system handles it all.
We use a button to keep data aligned
The error stemmed from a column formula that calculated the first and last days of a request based on the work schedule. When I copied the row, the day value broke because it was no longer linked to the original calculation.
To fix this, I moved the logic to a button and populated the column with the button’s output. This freed the column from formulas, allowing me to use other buttons to update day values as needed, ensuring I always have the latest data. Additionally, this approach keeps the document running smoothly, as most of the work is now done through buttons, minimizing in-table calculations
The button’s calculation, based on request and cancellation logic, primarily focuses on the last two choices to determine the next step. This principle is explained in more detail in my blog post on cancellations and double bookings.
Once the buttons are pressed, the updates are swift, resulting in an accurate balance. This reflects the example I provided: holidays and sick leave are booked and canceled correctly.
I hope this article shed some light on the topic and helped you solve a tricky problem! If you found it valuable, consider a donation to support my work.
My name is Christiaan, and I regularly blog about Coda. While this article is free, my professional services (including consultations) are not, but I’m always happy to chat and explore potential solutions. You can find my free contributions in the Coda Community and on X. The Coda Community is a fantastic resource for free insights, especially when you share a sample doc.
