Coda paid time off (PTO) — 4.0
How to set up and maintain the yearly holiday balance?
My last blog ended with this phrase:
In certain countries like Belgium, employees receive compensation or an additional day off when a public holiday falls on a weekend. However, in the Netherlands, that holiday is simply lost. Our next blog post will delve deeper into this topic, exploring the differing approaches and their implications for workers and employers.
If a country follows the Belgian model of compensating for holidays that fall on weekends, we need to incorporate an additional business rule. We will check per holidays and identify if it occurs on Saturdays or Sundays. We’ll then tally these weekend holidays and add that number to the employees’ annual leave balance.
Indeed every employee has the right to a certain number of days (or in the USA it is hours) per year. There is some variation per country. In The Netherlands these days are not based on your employee history as it is the case in Belgium.
It starts with a table that defines this rule. We list contract countries as we get them when people start or change a position. Via the function AddOrModifyRows() we add new countries when they arrive. A new country means we have to define the PTO rule: compensation, no compensation or observation.
No compensation means that we handle the supply of holidays as they present themselves, a holiday in the weekend is bad luck. Compensation means that after a holiday in the weekend, we add this day to the yearly balance. Observation follows country specific rules.
However, these calculations are only meaningful with country-specific holiday data and a yearly balance, which we’ll set up next.
Creating the yearly balance
We use a form to manage holiday requests and cancellations.
It serves two purposes: deducting holiday time when used and restoring it when cancelled.
Updating the yearly balance
Our yearly balance is based on a count of the holidays in DB Request and that count depends on the type of request:
This results in multiple rows in our main table, with one row per date, significantly simplifying calculations.
Using positive one for requests and negative one for cancellations allows efficient button-based handling. Let’s start by examining the button that generates the rows in this table.
My initial perception a few years ago of transposing as an advanced technique has shifted, as I now encounter its necessity in almost every Coda document.
This elegant approach of adding positive and negative one simplifies the calculation process, as evidenced by our subsequent button that generates a balance. Button activation is restricted to the relevant period through the use of a unique ‘key’ identifier.
This complex process involves multiple functions working in tandem:
- Buttons are active when the
workingdaysNett.IsNotBlank(). - The ‘root’ function determines the filtering criteria.
- Rows are dynamically adjusted based on the ‘root’ output.
- We update crucial values like consumed days and hours by appending new values to existing ones using
ListCombine(). - While day calculation is simple (using ±1), hour calculation requires chaining
workingdaysNett,work schedule, and we force the outcome in hours usingToHours().
This creates a table, as shown below, that tracks one day per row per employee, with data populated via the button in the request table.
To keep this blog concise, I’ll cover compensation day balance updates in the next post. But first, let’s explore briefly the rolling total, a topic I’ve discussed previously. Crucial is the comparison of dates.
thisTable.Filter(
people.Contains(thisRow.people) and
year.Contains(thisRow.year) and
theDate <= thisRow.theDate)
.consumedDays
.ListCombine().Sum()As promised, in our next blog we explore the compensation logic which requires an automation.
I hope this article was informative and helpful. Feel free to reach out if you have any questions — I’m 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.
All the AI features we are starting to see appear — lower prices, higher speeds, multimodal capability, voice, large context windows, agentic behavior — are about making AI more present and more naturally connected to human systems and processes. If an AI that seems to reason like a human being can see and interact and plan like a human being, then it can have influence in the human world. This is where AI labs are leading us: to a near future of AI as coworker, friend, and ubiquitous presence. I don’t think anyone, including OpenAI, has a full sense of all of the implications of this shift, and what it will mean for all of us. — source: Ethan Mollick from One Useful Thing.
More about Coda AI and Coda Brain:
