How to Coda Sick Leave Part I
Registering sick leave and counting employers contributions
When employees are absent due to illness, often the employer has to pay for a certain amount of days (or weeks) hence the government takes over. Rules differ per country, but the general idea is that there is some part to pay by the employer and an other part by the state or Insurance company. The employer part is equal to the normal pay, after you mostly get less.
In our example we assume that the first three working days (which exclude holidays and weekends) are for the employer, the rest for the state. However if it happens that you get ill multiple times per month, the same rule applies for each period. This makes it a complex calculation.
Happily we can rely on the ‘The Max Method’ to get started. This method permits to extract date-ranges from a list of dates and serves as the foundation for our main calculation. Merci Max!
In this first blog I’ll show how to calculate a standard situation. In my next blog I’ll dive into the unavoidable complexities related to the start and end values per of each month.
Getting started
In the table below I show per date the weekends and the holidays for visual inspection. In the case below, we only have pay days on the balance of the employer as of Tuesday January 4.
Step 01 → Defining the lists
We start by filtering the checked days per employee to group them. It is easy to have all the checked days, it is a bit more complicated with the employee taken into account when you create a canvas button or canvas formula. We have 10+ employees, but only four got ill in our example.
This is our first step.
In our example we have multiple periods and we need to keep them apart. Dates are like numbers so we can calculate with them and thus we subtract each date from the previous date. First we create the gaps for the periods you see below. The shown ranges permit for some serous testing.
We start by defining the gaps partly. However partly it is not enough, we miss the first and the last dates.
To solve that issue we add the first and last via a ListCombine() as you see below. When I started building the solution, I tested with one employee. It was only a the end that I had to come back to bring ‘TheEmployees’ into account, also in filter that is part of the ListCombine() :
I gave this function a name: GroundWork and use it to generate my final list of dates.
What we need are the dates between the start and end of each period and what better function than Sequence() to help us out. We apply a little trick to get a smart virtual index number. When you have 6 dates,you ask for the 1, 3 and 5 by adding 2 in the function, see below:
Since the end date is the number after the start date, you notice +1 in the second part of the Sequence() function. This gives us the result you see per employee.
Step 02 → CleanUp
It is one thing to get a list of periods, something else to evaluate each list individually. This function is our next subject. Days off due to illness in weekends and on holidays are not paid. We have to filter them out.
I learned the hard way that I had to filter this out as early as possible and in case the first or last sick days of each month only cover holidays or weekend days, you need a ‘back up’, this is the IfBlank() you see a bit hidden in the function.
We first count the number of periods. Via a Sequence() we attribute to each period a virtual number to reference via a FormulaMap() each item in this lists. Once this is done we can finally apply our calculation to each sub list (period) to make sure we only count the days one has to pay for.
Step 03 → Only this month
So far it was not easy, but a rather straightforward application of the CFL inspired by the Max Method. Coding Coda gets complex when the real world comes into play.
The business rules are that a period up to 3 days has to be paid by the employer, no matter when it starts, but every first day of the month we make a balance. This implies that absence started in the previous month has to be considered in case the employee is sick on the first working day that is not a holiday.
First we check if the first period covers the first possible working day of that month. If that is the case we start a complex calculation, if not we simply count the the days per period.
We also have to put dates apart in case they match the last working day of th month. For both I developed a function I renamed (MonthStart & Transerdays). We take the lowest number of the first period and the highest number of the last period via First().Min() and Last().Max() .
This was the relative easy part…
Part II → linking previous month.
This puzzle became far more complex than initially assumed. I’ll write about it in part II. The complexity derives largely from working with sub lists, splitting them and merging them later after several manipulations.
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.
