How to Coda monthly sick leave?
Update monthly overviews with a single click
Summer 2022 I posted about sick leave on for example Thursday, Friday, Monday & Tuesday in a context that only the first three days are paid via the employer. Since the weekend broke the consecutive range, I had to repair this chain in a specific way to notice 4 days in a row instead of 2 times 2 days.
It was only a few months later I came across work plannings including all sorts of variations like you see below.
This made me realize that the 5 days in a week and two fixed weekend days is a kind of implicit work planning we forget to make explicit while coding.
In this blog we reuse previous work and explore a method to calculate sick leave that deals with all sorts of work plannings, a planning that on top can change over time also during the sick period.
The blog is long, actually too long to become a well read blog, but once you have finished it and followed my instructions you get a table below filled out for you almost automatically. A small miracle under your fingertips.
To inspire and encourage you, I poured this work into a template. As always not for sale, nor to copy, only for inspiration.
What partly caused my need for multiple steps is related to limitations I wrote about in blog on What To Coda in 2023.
This type of work might be a doubt to do in Coda since it is far away from the promoted write ups and the to do lists due to its inherent complexity. Nevertheless, it works well and I am proud of this doc since it will avoid many headaches and double checks when you deal with irregular work planning.
Needless to say that the burden to get here was heavy to carry.
We reuse what we have
In our previous blog on counting the vacations per year we did foundational work. The aspect missing is the split per month thus filtering each date on on Month()and adding a row when we have to cover a non existing month and or year. We start by repeating these steps to get the dates and thus the hours.
Splitting vacation over years in Coda
Dealing with vacations covering multiple years
huizer.medium.com
- Via
Sequence()we get all dates - We filter out the holidays (and already approved vacations if that is the policy)
- We look per working date for the appropriate working scheme using a virtual index and
ForEach()combined withLast(). - Per date we get the hours
- We end up with two lists. One with dates and one with hours.
The applied filters result in dates and thus hours per day for only relevant working days.
How it works — step 01
Each time somebody gets sick, a button is pressed to confirm the sick leave. This button slices the sick days and distributes the values over two columns:
- the 3 working days for the company
- all other days for the government
The slice value is derived from a table that shows the consecutive working days the employer has to pay the salary. In our example — Bulgaria — we have 3 days. In Germany it is zero, in Belgium it is 7 days, in the Netherlands 2 years. There is a lot of variation in Europe. In the United States there is often an annual budget for paid sick leave and paid vacation expressed in hours. The related balance logic we showed in this template.
We use these numbers per employee to slice up the consecutive working days. This results in a logic as below. We have all the dates (we count them) and we put 3 in the box of the employer and the rest is for the government if there are any more days left.
The coding logic of this button that slices the date range:
You may notice the filter, this is to remove the blanks in the list we generated based on the work planning. In the last line there is a “+ 1” to make the slice starting as of position 4. These are all rather simple operations compared to what is coming next.
From here on it is getting difficult.
The main set up
Once we have the dates for the employer and the dates for the government we have to distribute the hours over the months & years related to these dates.
Below you see how a start date and an end date result in a date range, the related hours and the dates reformatted to a month & year value. The numbered list shows how the data is related. This visualisation is eye candy , but won’t help us any further in terms of coding.
How it works — step 02
The difficulty we face is that we have two lists: a list of hours and a list of dates. In the numbered list you see how they are related.
We start by putting dates together that live in the same month. Below how I did this. First I generate month dates related to the list of vacation dates. They have the typical Coda Date Format: 2022–12–01 or 2022–11–01 or 2023–01–01 and they permit me to apply chaining once they live on a row in a column (list) formatted as date.
this results in what you see below:
I continued with the function, turned the items into dates, counted the positions and I defined the years & the months (both as numbers) to use in my filter that is completely composed out of 4 ‘named’ functions — below all in white.
This results in what you see below and right you see the amount of lists each raw list is composed of characterized by the squared brackets.
Below you see how this list logic works when we enter “ 2 “. then you get the second list (if there is any).
We now can isolate the sub lists and count per sub list the items. In these lists they are the dates, but in an other column we have the hours. Once we understand how to slice the dates in monthly pieces, we can slice hours in monthly pieces as well and we can distribute the sum of the hours over the respective months. List one in row one, list two in row two, list three in row three and so on.
We start by counting the items in each sub list:
Second we prepare a slicing logic that deals with one sub list or 2, 3 or 4 (or more). A list always starts with position one. In our example you know that your code works if you can slice along these values:
- 1–4
- 5–21 (17 + 4)
- 22 (21 + 1)
As a demo I created the following to show visually how this works:
We need a variation of the above that automatically fills-out the correct values in our Slice() function. Once we slice the raw lists with dates into months, we can do the same for hours, same slicing, different list. Below in 5 steps how we create these lists
Step01: each list starts with position one, so we add the number one to the list using ListCombine().
Our next step (step02) is to create pairs, numbers that we need to add up:
The first list contains one item, the [1], the second two: [1,4] and the third three as you can see: [1,4,17]. In the Sequence() we start at one, but we only get the list of 1,2 and 3, although the newly formed list counts 4 items (the new ‘one’ plus the three we had). The reason is that we only need as many positions as there are elements in the list and so we added the minus one at the end to compensate for the one we added at the beginning. With these pairs ready we can can get the sum of each list in step03:
The next step is to create the counter part: 4, 21, 22 and for this step04 we rewrite a bit what we had, we make it shorter and simpler. We don’t need the extra one in this list.
In step05 we bring the insights nicely together as you see below using a List() to show how the pairs are related. This is eye candy. In our calculation we need the living numbers slicing through the raw list (and not the numbers isolated and congealed in a list).
Our final step step06 uses the logic we put prepared in step05 and this step will slice the list as we planned for.
The filter on IsNotBlank() is required because the raw data contains empty lists. Let’s see how this works for the list of hours. As expected this works fine as well. We only replaced the data set, hours instead of dates.
Since we can isolate the months inside list of hours, we can create the list below. Again, it is eye candy only. Sometimes it helps to see this type of info. It confirms we are on the right path.
Some readers maybe remember I wrote about a comparable issue a while ago. It was about using a list of numbers (for example: 1, 13, 17 ) to get pairs like 1 — 1, 2 — 13, 14 — 17. In this blog I use a Sum() and a Slice(), while in my previous blog I worked with a double ForEach() .
Your doc, your choice.
The above logic applied — step 03
For the date ranges related to the employer and to the government, we need to set up a logic that follows the described pattern. First we add to the button two functions to have the hours for the employer and the government. Next per date range we:
- identify the unique months
- turn the dates into groups based on these unique months
- we count the dates in each group, thus per month
- we define the values necessary to slice the hours (eye candy)
- we calculate the hourly sum per month
Below you see how I created the steps for the government. The underligned values may help you to understand the process. Remember in our example the government takes everything above 3 days (regardless how many hours per day the employee works).
The last and underlined step Gov_Step06 you find below. It follows the same pattern as previously described. I noticed I made easily mistakes while duplicating the logic.
I repeated the same pattern for the hours of the employer. It is important we do so, because you can have one or two days in one month (and year) and the rest in an other month (and year).
The result is what you see below and our next steps are:
- creating new months & years in case they are not yet present in the balance table → Step 04
- modifying the rows per month and keeping track of the consecutive changes per month. → Step 05
Creating and or updating rows — step 04
Shortly after I created the Step06 logic with the hours per month, I realized that I needed a formulaic relationship between the hours and the months to make it possible to update rows or add rows that come in between (Nov between Oct & Dec for example). By keeping the hours apart from the months, I had broken an intrinsic entanglement. I had to, the Coda Code Editor Screen allows not for much (Jan 2023). To fix what I had broken on purpose, I created step07:
All I had to do was to wrap step06 into a list together with the months from step02. This step07 logic permits me to filter on the month and on the year to check for the existence of this period.
The Step07 logic permits me to relate to the first element (the period) of each sub list and use that in my filter.
Step08 gives back the rows in the balance that match with the Step07 values (periods). I named the outcome of the filter ‘present’. When you want to add something, you need to know that it is not yet present, because if it is, you need to update (Modify). In step09 we define the values not present as rows in the balance.
Using the Not() we get the inverse resulting in values not yet present in the balance. In step10 we count the items from step07 not yet present in the balance.
The step07 items contain the periods and the hours. To add or update the balance we filter on the period and we add or update the hours. The hours are the last part in each sub list. Below you see how we get them out using Last():
We create a logic as you see below. Based on the start & end date of the sick leave, we calculate the hours per month and after we press the button, the sick balance gets updated. For now we only have the part of the government, but I duplicate and replace this logic to cover the employers side as well. Below how it finally looks.
Below the main structure. A button to modify & a button to add rows. It is simply not possible to get so much code into one editor and keep it functional. First modify then add rows to avoid you have double count.
The button that deals with the row modification you find below. Notice that inside the Sum() function we need the named function again. Without this named function, you take all the hours of the balance table, and although you don’t see this at first when you have only one row to modify, you notice it shortly after. This was my case as you can imagine.
Main elements are the named filter dealing with present values and the named function in the Sum() to add hours to the months they belong. I added also the function to count the days, which is practical in many cases. To avoid unnecessary calculations we disable the add button when there is nothing to add, like wise for modify when there is nothing new. The main button is disabled after execution.
Further improvements & next steps
After a long long period of coding this doc, it works. I reduced the employees to a few people and all but one have various work plannings. This permits you to play around. Although even playing around easily may feel complicated. You need to create per employee a work planning in 3 steps. That cannot be done for you. In any company somebody has to keep track of this. The rest is for Coda.
Our next blogs
We have stretched the limits of Coda with this exercise a bit further. As mentioned earlier, the Coda Formula Language is not the barrier, nor the performance. It is the lack of overview and support inside the software that forced me to break code into pieces to glue it together again (in the step07). Once you have put this part in place, it is easy to maintain, unless the 3 days in the example become 4 days or 5 days. As of that moment you have to adapt the business rules a bit while meanwhile applying 3 to the concerned dates. I wrote about this in this blog.
An other issue I thought about is related to the work planning. For the moment we have a week planning, based on the logic of a work week. However we can imagine situations people only work every two weeks or every first week of the month etc. Although the current set up could handle it, for the user it is inconvenient. I like to believe that using Coda should make things easier and so we may have solve this puzzle as well in the months ahead.
Once solved this extra puzzle, we could apply the complete logic for vacations and keep track of the hours per month. We can also use this logic to plan work and budgets (hours per day related to a fee per hour per employee, per period etc.). The granular logic in this set up permits for great flexibility in any calculation.
My name is Christiaan and I support SMB with calculations (budgets and planning) and I prefer using Coda to get the job done.
Coda comes with a set of building blocks ー like pages for infinite depth, tables that talk to each other, and buttons that take action inside or outside your doc ーso anyone can make a doc as powerful as an app (source).
Not to forget: the Coda Community provides great insights for free once you add a sample doc.
If you enjoyed this read and would like to get more Coda related content, please consider a Medium membership. It is it only $5 a month, and you’ll have access to every article ever published on Medium. If you sign up using my referral link, I’ll earn a small commission.
