No Sick Leave in the Weekend
A Coda puzzle with a subtle complexity
After finishing my second blog on sick leave about a month ago, I felt relieved for a few reasons. First of all I was happy that it all worked well, but also I was happy to escape the Coda inertia. The code editor became so slow that any change required an unfair amount of patience on my side.
However I am not done yet as it appears.
The developed logic deals with a list of uninterrupted sick days as indicated by the HRM employee in a table and this defines the days paid by the employer and the days paid by the government.
The challenge
In the overview below we have two lists of two days and the current output is that the employer has to pay two times two days, thus 4 days in total, while the correct outcome is 3 paid days by the employer (…) because the employer has only to pay the first three working days.
This issue would not appear in a logic that asks the HRM employee to set the start and end date of a sick leave. In this approach you would not consider to leave out the weekends or holidays and we would have an uninterrupted list of dates. Though we considered — from — to — rows as input fields, we made the choice for the logic as you see above. Only clicking and most important: all in once place per day (work, illness, paid holidays etc). Again, software should make things easier, not harder.
Preparation
We need uninterrupted ranges of days that bridges weekends and holidays.
I explored three approaches in the order as you find them below.
First, the IsoWeekday() numbers for the weekend are 6 and 7 (Saturday & Sunday). Once the last day of a period is a Friday, thus ‘a number 5’ we can automatically add day 6 and 7 to the date range to have an uninterrupted range of days. However this does not work for holidays, nor does it work in a scenario that an employee by mistake also marked the Saturday but forgot the Sunday. This approach lived a very short time.
The second approach is based on dates related to weekends and holidays. Here a certain complexity comes into play. We cannot say that in case the last day of a sick period is followed by a date that represents a weekend or a holiday, we add this date. Already in case of a weekend it won’t work (two dates) nor in case of a weekend followed by one or multiple holidays. The complexity is that we have to add mini lists containing consecutive dates in case the first date of this mini list follows the last date of the period.
This version resulted in some complex code I am not diving into right now. While I was happy with the outcome I wondered why so complex?
Our subtle complexity
The concept I finally applied is simple. We assume that on all weekends and holidays employees are sick as well as on some normal days. The normal days we get via our source table, while the list of dates related to weekends and holidays is generated via a canvas formula. These two lists we blend together via a ListCombine() and a Sort() while the Unique() makes sure that we only have one date per day in our list.
This new list becomes the source for our calculation of the sick leave periods I wrote about in this blog. These periods span in this approach by default weekends and holidays and thus start and end dates glued to these non working days create periods with days off included. The first result is a list with far too many sick days, but that is okay for now. We wanted sick days glued to days off to make it possible to treat these periods as one. And this works.
The next step is to take all theses periods and days per period and filter per period out the days off in our actual calculation. This works as well.
This is what I like to call a subtle complexity. I did not have to change many elements, only on a few places I reworked the code, but the result is stunning.
Previously a filled out table like below resulted in two periods, because it was separated by a weekend.
In the updated version it is actually one period what according to labour law it is and the calculation gives back 3 days paid by the employer.
This exercise spanned two days. I am not 8 hours per day working on any problem (max 6–7 hours concentrated work per day), but certainly the second option took several hours to fine tune. The integration of the concept — once I saw it — went rather fast, but checking all sorts of scenarios takes time. You don’t want to run into unforeseen issues. This was mainly about finding the right concept. Often when things get complicated I take a step back to explore if there are no other ways and yes often there are.
I hope you enjoyed this article. If you have questions feel free to reach out. Though this article is for free, my work (including advice) won’t be, but there is always room for a chat to see what can be done. Besides you find my (for free) contributions to the Coda Community and on Twitter
My name is Christiaan and I support SMB with calculations (budgets and planning) and I prefer using Coda to get the job done.
Not to forget: the Coda Community provides great insights for free once you add a sample doc.
