Coda the compensation day in hours
The Belgium compensation day reviewed and refined
Last summer, I stumbled upon a fascinating quirk in Belgian employment law: compensation days. It turns out that if a public holiday falls on a weekend, you don’t just lose that day off — you get compensated for it! In most countries, a weekend holiday is simply bad luck, but Belgium has a different approach.
My initial thought was simple: just add an extra day to the employee’s holiday balance. Easy, right? I even started imagining a system where I’d find the compensation day, add a “-1” to the balance (meaning one extra day), and let employees use it before the year’s end.
But there was a problem. This approach works fine for traditional full-time employees, but Belgium has a diverse workforce with many people working part-time or having varied work schedules. Simply adding a full day didn’t account for someone who might only work a few hours on the day that would normally be “compensated.”
How do we figure out the actual compensation owed?
After some digging and expert advice, here’s the solution:
- Identify the next working day after the weekend holiday. This is often a Monday, but could be any day an employee normally works.
- Calculate the number of hours the employee would have worked on that day. This is crucial for part-time workers or those with irregular schedules.
- Grant compensation for that exact number of hours. Someone working a half-day on Tuesday as first working day gets half a day of compensation (4 hours or 3.5 hours-, while someone with a full 8-hour day on Wednesday — as the first working day in the week — gets the full 8 hours.
This ensures fair compensation for everyone, regardless of their work pattern.
How do we program this?
So, I’m revisiting my initial system for handling national holidays. It already does a great job of identifying upcoming holidays and flagging those that fall on a weekend in Belgium. As explained, instead of only automatically adding a “-1” (representing a full day) to the balance, we need a more nuanced approach.
The goal is to calculate the exact number of hours to compensate, based on the employee’s schedule on the first working day after the weekend holiday. This means we need to dive into their work schedules and consider a few things:
- Identifying the “next working day”: This might be straightforward (usually a Monday), but we need to account for variations.
- Schedule validity: We need to determine which work schedule is active on that specific next working day. Remember, schedules might change, especially around holidays!
Once we’ve successfully navigated this maze and found the relevant work schedule, we can extract the number of hours the employee would have worked on that day. This is the magic number we need for accurate compensation!
We reuse what we have
We have an automation that identifies Belgian holidays that fall on weekends. This system then adds this holiday to our primary database (DB PTO) and automatically adjusts the leave balance accordingly. Based on the table below, you can see how the balance will be reduced by one day. This section is necessary because the organization requires two separate records for paid time off: one for the number of hours and another for the number of days, regardless of their length.
First step in getting the hours
The process begins with identifying the date of a Belgian holiday that falls on a weekend. This date is then compared against the most recent working schedule for each Belgian employee. This comparison is the first step in our process.
We identify the next working day based on the employee’s schedule at the time of the holiday. However, schedules can change. An employee might switch to part-time work or adjust their days.
For instance, in the example of July 21, 2024, if an employee’s schedule changes on July 22nd to a Wednesday start, then their next working day would be Wednesday, July 24th, not Monday, July 22nd. Similarly, if the employee modifies their schedule on the 22nd to work Mondays and Wednesdays instead of Thursdays and Fridays, then their next working day would be Monday, July 22nd and not the Thursday.
Essentially, we need to factor in any schedule updates made after the holiday to accurately identify the employee’s next working day.
Two steps
To determine the next working day, we identify the active working schedule and extract the weekday number of the first working day within that schedule. It’s important to note that this depends on your Coda settings, which can sometimes be a bit tricky. In this case, we’re using the isoWorkdays() setting, where Monday is considered day number 2.
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.
[DB PTO Active Schedules].Filter(
people.Contains(thisRow.people) and
fromDate <= thisRow.fromDate).reference.Last()
.alldays.ListCombine().WithName(wrkDys,
[DB Days].Filter(CurrentValue.Contains(wrkDys)).nbr.Min()
)
.WithName(nxtValue,
If(thisRow.weekdayName.Contains(Saturday),
thisRow.fromDate + nxtValue,
thisRow.fromDate + nxtValue - 1 )
)This code snippet first calculates the next working day, assuming the holiday and the following day have the same work schedule. However, because schedules can change, we need to double-check. To do this efficiently, we compare the work schedule of the holiday with the schedule of the calculated working day. Using this comparison, we can accurately determine the next working day and then find the corresponding working hours.
The last and final step — to get the hours
To determine compensation hours, we need a reference date and that we got. I’ve divided the calculation into two columns for clarity: one for the replacement date and another for the hours based on the related work schedule.
From the active work schedule table, I select the schedule matching the employee, date, and weekday (i.e. Tuesday). Then, I retrieve the related hours and multiply the result by minus one to get a negative number. This is added to the balance, effectively reducing it. In this example, the employee works a 38-hour week over 4 days, so a compensation day equals 11.5 hours in this scenario.
Overview of what we did
We encountered a challenge in our system regarding holiday compensation for employees in Belgium. Belgian law requires adjustments for holidays falling on weekends. Initially, we assumed simply marking these days as working days would suffice, but it became clear that the actual working hours of those days were crucial for accurate compensation. To address this, we implemented an automated solution. This automation daily checks for holidays falling on weekends and, if found, activates a process only for employees with Belgian contracts. These employees then receive an updated holiday balance through a dedicated button. This process involves two steps: first, calculating the hours based on the relevant working schedule and second, adding those hours to the employee’s balance via an other button. While the underlying calculation is complex, this automation effectively handles a common and practical issue arising from Belgian labor regulations.
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.
