How to Coda probationary leave
hire date + 90 days
A probationary period is a stretch of time during which a new or existing employee receives extra supervision and coaching, either to learn a new job or to turn around a performance problem. The probationary period can be as short as a month or as long as a year, depending on the situation, and often companies will use a 90 day probation period. source
The probationary period is about calendar days. It is the hire date plus (often) 90 days. Holidays, unpaid time off or sick leave do not change this principle (as I initially assumed). On day 91, employees will begin to accrue and use vacation and often will have 40 hours at their disposal for time off.
The challenge
Hire date + 90 days is a simple calculation. The issue is that once you ask for time off or you fall sick and it is at the end of this period and during the time off you move over to the post probationary period, it effects salary related calculations. Our challenge is to deal with that properly.
Step — 01 — evaluate each date
We apply a basic operation and we check for each date in this range if it is within the 90 days. In the example below you see this is partly the case. Some days are out of range, thus part of the post probationary period.
Step — 02 — create a lookup
In the table below you see we work with a lookup. This one contains values that correspond with the date ranges depending on the rules we define.
Step — 03 — refine the outcome
It may be that some dates in this range are non working days. We filter out the holidays (which are none in this period) and we take into account the week planning of this specific time off period. This subject I discussed to a large extend here and deals with working weeks of variable length. Below you see how the code is shaped.
The result is a list of dates that relate to true & false. In this scenario we have 3 possible outcomes we coded in the above screenshot.
- all are true — probationary period
- all are false — post probationary period
- mix of false & true — mixed probationary period
As you see, we get the right type by filtering and counting.
Step04 — dealing with the result
We applied a mix of previous applied logics and added a tiny layer of something new: the countUnique() logic. All rather straight forward.
I try to limit the time needed to make this logic work. Instead of creating 3 scenarios, I stayed with two and I blocked the scenario that results in the mixed version. This is for the user inconvenient when it appears, but it only happens in rare cases, very rare cases.
Below how I blocked the scenario. I put a rule on the ‘until’ field. Again chances this appears are little and do not justify the huge amount of work — in my opinion — to take away all friction.
In my blog below I wrote that Coda does not yet support power users very well. The more complexity you inject in your doc, the harder it will be to maintain. That is an extra cost you want to avoid as well.
The rule I applied looks like the one below. I check if the outcome of the ‘true & false’ evaluation is 1, if so all good (all true or all false). In case the count is 2, there is the error message and I ask to split the range in two.
That is all.
Coding Coda is also about choices and this is one of them. In my opinion it is okay not to pursue all scenarios when:
- you have solution (here: split the date range in two)
- they are rare
My name is Christiaan and I support SMB with calculations (budgets and — Human Resource — planning) and I prefer using Coda to get the job done.
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.
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.
