Work schedules refined in Coda
Adapting to recurring schedules of any interval like bi-weekly
Building a flexible and transparent PTO (Paid Time Off) system starts with a well-defined work schedule. As you can see in the table below, I’ve created a structure that accounts for different shift types, durations, and even country-based variations. This post will walk you through the logic behind an advanced version of it and show you how to implement this in your own Coda docs.
We start with the creation of work schedules. As you see it is row based.
Once we’ve defined our work schedule variations, we need an efficient way to apply them to our employees. That’s where this handy form comes in (see image below). With just a few clicks, we can select the employee, set the effective date (even for future scheduling!), and assign the appropriate work schedule variation for each day of the week. This ensures that everyone is on the correct schedule and simplifies PTO processing down the line.
Once the form is submitted, an automation takes over and neatly transposes the data into a central table (see image below). This table becomes the single source of truth for all our PTO calculations, ensuring consistency and accuracy. This approach works particularly well when work schedules are relatively stable, with changes occurring every few months rather than every other week.
The need for refinement
In today’s dynamic work environments, employee schedules often change frequently. This demands a flexible and automated system to avoid constant manual updates. In this post, I’ll introduce a solution that handles recurring weekly patterns and simplifies the process for PTO administrators.
It all started with an email from the client that contained a phrase that one employee worked during the even weeks on Saturday and on the odd weeks on a Wednesday.
Step 01 — getting the feeling for the weeks
To find a more robust solution, I expanded the problem beyond its initial limitations. Instead of focusing on fixed weekly or bi-weekly patterns, I explored scenarios with recurring schedules every 3, 4, 5, or more weeks. This broader perspective revealed a pattern solvable using the Remainder() function which is also known as the modulo operator (%).
This operator, represented by the percent sign (%), gives the remainder after a division operation. For example, 11 modulo 2 is 1, 11 modulo 3 is 2, 11 modulo 4 is 3, and so on. This is a basic mathematical concept typically taught in early secondary school.
While the concept itself is straightforward, its application can be tricky. To help you understand, we’ll break down the setup process step-by-step, starting with how the modulo operator works.
The previous logic doesn’t hold up for schedules that occur every week or every other week — these are important variations we can’t overlook. Therefore, the solution requires a more layered approach, as shown below.
I’ll start by explaining the logic in a step-by-step manner using the SwitchIf() function, and then I'll present a more concise version. You can choose whichever approach resonates best with you.
The formula begins by checking if the selected interval is “every week.” If it is, the result is automatically true, and no further calculations are needed.
Next, the modulo operator is applied twice. The first application checks for “every odd week.” In this case, the remainder of the week number divided by 2 should not be zero. For all other intervals (“every third week,” “every fourth week,” and so on), the remainder should be zero. This “remainder equals zero” condition acts as a catch-all for the remaining variations.
Now, let’s look at a more concise version of the formula. As you can see in the screenshot, we’ve utilized the built-in IsOdd() function, which simplifies the logic previously achieved with the modulo operator.
While understanding the modulo operator is helpful, we can simplify our formulas by taking advantage of Coda’s built-in functions.
Step 02 — adapting the form
Not only do we have to adapt the appearance of the form, but also the filters inside the relations. So far the assumption was that people only worked during regular working days, but that all changed after the email I received. Below what I made and what needs to change.
I then removed the condition excluding weekend days. The form itself changed as well. I added the interval logic in relation to each working day.
This step makes it easier for the PTO administrators to select the correct work schedule when they define work schedules.
Step 03 — richer work schemas
Next, we transpose the new data to our active schedules table. This table is crucial because it helps us determine working days, hours, and even break times for any given date.
This all works well and we now have a table that per day includes the interval logic as shown below.
Step 04 — add conditions to main code snippet
This extra filtering capability enhances the system’s flexibility, and brings us to the core of this exercise. Previously, we evaluated a date to first determine the corresponding work schedule. This was particularly crucial for situations requiring historical adjustments, like when an employee falls ill during their vacation. In such cases, both holiday and sick leave balances need updating. Second and within the active work schedule we looked for the weekday name. I opted to use the workday name directly in the filter to avoid potential inconsistencies with the weekday() function, which can be affected by document settings.
Furthermore, by including the “interval” variable, we refine searches even more precisely. Using the isoWeeknumber() of any given date, we can apply the modulo operator logic we developed earlier to accurately identify recurring schedules.
This screenshot shows a powerful formula that tackles the complex challenge of managing employee time off with varying work schedules.
We build upon the previous modulo-based logic to determine working days. This code snippet, which includes SwitchIf and Remainder functions, is now integrated into a larger function and part of a button. This button serves a crucial purpose: it "hardcodes" the results of the calculation. Why is this necessary? Because work schedules can change, and we need a reliable record of past schedules to accurately adjust time off in case of corrections or retroactive changes. Imagine an employee who takes time off during the summer when they work fewer hours. If we rely solely on active formulas, any future changes to their schedule could disrupt past records. This button ensures that the calculations are based on the work schedule active at the time of the request, providing a historical record and preventing discrepancies.
This solution, while complex under the hood, simplifies PTO administration significantly. It accommodates diverse and changing work schedules, ensuring accurate time-off tracking and adjustments. Although developing this solution required considerable thought and time (around 8 hours total), it’s far more efficient than custom programming in other systems.
This robust, Coda-native solution effectively addresses a real-world challenge, demonstrating the platform’s power and flexibility. And with Coda constantly evolving, we can anticipate even more streamlined solutions in the future.
I hope this article was informative and helpful. Did it help you to solve a problem you unlike would have solved other ways? What about a donation?
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.
Grammarly, the renowned AI-powered writing assistant, has acquired Coda, a versatile productivity platform. This strategic move has sent ripples through the tech world, raising questions about the motivations behind the acquisition and its potential impact on both companies and their users. This article delves into the details of the acquisition, exploring the reasons behind it, the benefits for both companies, the reactions from the Coda community, and the potential implications for the future of AI-powered productivity. More info here.
