The next working day in Coda
Compensation days part III for Belgian employees
As I explained last summer and in my previous blog post, the compensation hours are linked to the number of hours worked on the next working day following the weekend holiday.
For example, if an employee normally works Wednesday, Thursday, and Friday, with Wednesday being a half-day, their weekend holiday compensation would be based on that half-day Wednesday schedule. This means they’d receive half a day of compensation instead of a full day.
To further complicate matters, we need to factor in those complex work schedules based on the modulo operator magic we discussed earlier. If someone works every second or third week, this needs to be reflected in the compensation calculation.
It took me some time to understand how to adjust the code snippet I already created last summer and you find below.
To understand how this works, I recommend reading this blog post, which provides a step-by-step explanation. It might seem complex at first glance, but the examples in the post will clarify the logic. You’ll also learn why we use the “Saturday” logic at the end.
To find the adjusted holiday date, we start by finding the smallest number in our list. We add that number to the original holiday date. However, if the holiday falls on a Sunday, we need to make a small adjustment. Since Monday has an ISO weekday value of 2, we subtract 1 from our calculated date to shift it to Monday. We don’t need to do this if the holiday is on a Saturday. For example, for the national holiday on Sunday, July 21st, this process would result in a date of Monday, July 22nd.
Building the solution
To determine an employee’s active working days, we first need to access their work schedule. This schedule outlines their working days, any recurring weekly variations (e.g., working every second or third Wednesday), and the corresponding hours for each day.
For example, an employee might have a schedule where they work every other Wednesday. This means the remaining Wednesdays are considered non-working days. This adds complexity, as we need to account for these variations.
To illustrate how holiday replacement works, let’s consider this scenario:
- Work Schedule: This includes the employee’s standard working days, any recurring variations (e.g., working every other Wednesday), and the daily working hours. It’s important to select the correct work schedule that applies to the relevant period.
- Holiday: Sunday, January 12th (fake holiday)
- Employee Contract: Belgian contract
In this example, the holiday falls on a Sunday, which is a non-working day for this employee. However, since they normally work Saturdays, we need to determine the appropriate replacement day and corresponding working hours for the Sunday holiday.
Step 1: determining the correct replacement day
The first part of the formula (highlighted in the image) focuses on accurately identifying the replacement day for the holiday. It begins by linking the holiday date (fromDate) to the employee's work schedule using the DB PTO Active Schedule.Filter() function (highlighted in red). From there, it extracts all the working days from the schedule using alldays.ListCombine() (highlighted in green) to determine the numeric day value for each.
To find the closest working day to the holiday, the formula uses the DB Days.Filter() function with nbr.Min() (highlighted in purple). This applies the "Saturday logic" explained in the previous blog post, ensuring we correctly handle holidays falling on non-working days.
Once we have a candidate replacement date, we apply the code snippet (highlighted in red) that uses the modulo operator. This snippet, which we discussed earlier, accounts for recurring weekly variations in the work schedule (e.g., “every odd week”, “every third week”).
Finally, the formula filters the results based on the employee (people) and the IsoWeekNumber() of the calculated replacement date (step01) to pinpoint the exact replacement day. This multi-step process ensures that we accurately determine the replacement day, even with complex work schedules and holidays falling on non-working days.
Applying the First() function to the results gives us Thursday as the replacement day, rather than Wednesday. This might seem unexpected, but there's a key reason: the employee's work schedule specifies that Wednesday is a working day only in even weeks. Since the holiday falls in week 3 of 2025 (an odd week), we must skip Wednesday and select the next available working day, which is Thursday…
Step 2: Handling Changes in Work Schedules
The formula also addresses a crucial scenario: what happens if the employee’s work schedule changes on the replacement day itself? To handle this, we need to verify the active work schedule on that specific date. This logic, which we covered in detail in our summer post, is implemented in the subsequent steps of the formula.
As shown in the image, the first part of the formula is named part1. This section, as discussed earlier, determines the initial candidate for the replacement day.
Next, the formula includes a second part that mirrors the first but with a key difference: instead of using the original holiday date, it uses the date identified in part1 to check if the same work schedule is still active. This involves repeating the process of finding the closest working day, applying the modulo operator logic, and filtering for the correct day, ultimately resulting in part2.
By comparing part1 and part2, the formula can accurately determine the final replacement day, even when work schedules change.
Step 03: determining the final replacement date
Once we’ve identified the correct work schedule (highlighted in green), we can determine the precise replacement date. This involves a step similar to the “Saturday logic” we discussed earlier.
As shown in the image, the formula uses DB Days.Filter() to find the closest working day based on the active schedule. This function identifies the weekday number, selects the first (or minimum) value, and names it theNextValue.
Finally, the formula applies the familiar If(weekdayName.Contains("Saturday")) logic to adjust the date if necessary, ensuring the final replacement date (theDate) accurately reflects the employee's work schedule and any weekend variations.
Step 04: calculating the final hours
The final step involves calculating the number of hours associated with the replacement day. This uses the previously determined replacement date (theDate) and links it to the employee's work schedule to find the corresponding hourly value.
As shown in the image, the formula uses the DB PTO Active Schedule.Filter() function twice. The first filter identifies the relevant work schedule based on the employee (people) and the replacement date (replacementDateComp). The second filter refines the selection using the reference, alldays, and weekdayNameReplacement columns, which contain intermediate calculations performed in separate columns.
These filters effectively pinpoint the correct entry in the work schedule and extract the netDuration value (highlighted in purple), which represents the number of hours for that day.
Finally, the result is multiplied by -1 to indicate that these hours are being given back to the employee.
Developing this formula was a challenging undertaking, requiring several hours of focused effort to design, test, and document. While Coda’s flexibility allows for such complex calculations, it’s important to consider whether this level of complexity is optimal given Coda’s strengths in other areas.
Documenting the process, as I’ve done here, proved invaluable for maintaining clarity and ensuring accuracy. Ultimately, this methodology guarantees that Belgian employees receive the correct compensation, which was the primary goal my client sought to achieve.
Hopefully, this post has shed some light on this common Coda challenge and empowered you to create more interactive and insightful data experiences. On a personal note, creating these in-depth posts takes a lot of time and effort. While I love sharing my knowledge, a little support goes a long way. If you found this helpful, what about a donation or sharing this post with your fellow Coda enthusiasts? Every bit of encouragement helps fuel the next 49 blockbusters!
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.
