Coda paid time off (PTO) — 5.0
Holiday compensation visible on the holiday balance
I ended my last blog with :
As promised, in our next blog we explore the compensation logic which requires an automation.
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. In this blog we show how to update the holiday balance for employees that have contract countries.
A crucial table is the one below:
Next we have a table with holidays
The Belgium national holiday is this year on a Sunday and since it is fixed date type of holiday (not a Christian holiday like Easter or Christmas) we have to compensate for this holiday.
Our first step is to give this holiday a different color to indicate its fits the compensation criteria and for that we need a formula:
In this context, I avoid using weekDay() due to its inconsistent results across Coda's regional settings. Instead, using explicit day names ensures accuracy. By taking this approach, we're closer to our goal: subtracting one (-1) from the holiday balance table and creating a new row if needed for the current month.
Employees with Belgium-specific contracts benefit from this rule, as the National Holiday is a Belgian holiday. Unlike observation days, they can use these days at their discretion. That explains why we add -1 to the balance.
The automation broken down in steps
We have a special time-triggered automation that runs every night at 4 AM. It doesn’t need any data input, which sets it apart from our other, data-driven automations.
Let’s add the condition. You’ll see the first part below for reference.
Step 2’s Boolean output requires an expanded code snippet. We’ll add logic to compare the new date against today’s date.
This code snippet evaluates to true or false and that is what we need. Only in case it is true, the automation can move to step 3.
Making use of what we already have
Initially, I considered directly modifying holiday balances using complex filters in combination with an AddOrModifyRows() in the automation. While possible, a simpler solution emerged. The main concern with direct adding and or modification was verifying its accuracy, requiring manual checks or additional monitoring.
Instead, we streamlined the process. We introduced a new variable (as shown below) and created holidays through an automation, adding them to the holiday helper table. By following this established and tested workflow, we ensured a smooth and reliable outcome.
This key insight led me to automate the process, creating a streamlined solution. The notes filled out via the automation and the balance type ensure clarity. The automation adds a day to the balance each time it runs, which accounts for the deduction of one.
Below the function addRow() applied.
The fields for ‘from’ and ‘end’ dates, as well as ‘balance type’ and ‘notes,’ are self-explanatory. The remaining code sections are tailored to the specific tables used in this project.
This results adds rows to our request table (one row per date) and next we created and updated the balance. For testing I runnend the automation twice and it all went well.
Initially, Step a 4 logic successfully triggered the buttons in my holiday helper table. However, the function unexpectedly began failing, requiring the addition of a new formula in step 3. This workaround seems unnecessary and was quite surprising.
Anyway, it all works fine with this addition in step 3.
This exercise demonstrates the importance of flexibility in flow design. While shortcuts may be tempting, sticking to established logic and utilizing new variables when necessary ensures maintainability and allows for future enhancements, such as Slack notifications for holiday balance changes.
Next blog — next working day
Ever wonder how to figure out the next working day after a vacation, especially when Mondays aren’t always the answer? It’s a trickier question than it seems, and one that can have a big impact on employee notifications. Our next post unravels the mystery and offers a foolproof solution.
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:
