How to Coda the next working day?
Replacing part II of Workday() — part 01
In my first blog on the matter I explained how we can replace the Monday — Friday logic with a next working day function that deals with a working week of 2, 3,4, 5 or 6 days provided that we talk about consecutive days. This gives us a flexibility the native Coda function Workday() is missing.
However the function Workday() solves a second problem elegantly. It permits the user to define a list of holidays and this part of the function makes sure that the next working day does not coincide with another day off.
To simulate this function we need a list of dates that represents the days off. It requires us to skip the days off and to take he first available working day. This is a rather complicated exercise with an unexpected beneficial outcome. Since this article became too long (I noticed while writing), I split it up in two parts. This one goes as far as the list logic. In my next article you find the working solution and a link to a document I used to create examples.
The creation of Sub Lists
It happens that the next working day coincides with a holiday or an already scheduled vacation. In this scenario we skip the holidays or vacation and take the day after. To avoid that the day after equals a non working day we have to make some steps. We create sub lists related to the following topics:
- holidays
- planned vacations
- non working days
The Non Working Days
In our previous article we assumed a workweek of consecutive days and not a work week schedule we see in countries like The Netherlands : Monday, Tuesday and Thursday are the working days. Wednesday, Friday, Saturday & Sunday are then the days off.
When we create a sub list of non working days, it does not matter which day is a non working day. Below you see the version from the previous blog and the actual version we will use with the separate days. The work regime is easy for companies when all people work 5 or 6 days a week at any given moment in time. Showing per employee the working days per period offers more flexibility. We will continue to use the latter.
In the above screenshot you also notice that that we work with a start & end date. However in the context of an open contract there is no end date. We focus on this issue before we move on to the holidays.
The Open Contract — No End Date
Comparing requires data and with a blank ‘until date’ as such we cannot work. We have grosso modo two options to solve this little puzzle. We calculate with a fake date to fill in the blank or we use the ‘from’ date as starting point. To avoid work arounds we continue with From instead of Until and as a result of this we look at dates that are after or equal to the from date. In the function below you see the ‘opposite’, that is because we look up dates in the table DB Work Regimes and we thus work the ‘other way around. This may feel confusing, simple create a table like below and test it, you rapidly understand the mechanism.
When the from date is not covered, the result is empty (row 2) and when it is covered, it generates a list of matching rows. From this list we take the last as you see below. Last and not first because the starting point is an other table. Yep, confusing, I know, but again I encourage you to try it out in your own docs.
By the way, we can create a function to generate the until date for us and leave it blank in case there is no next period, see below.
I decided to use this function in my docs, the user only fills out the ‘From’. The less to fill out, the smaller the chances to make mistakes.
The Holidays.
The list of holidays comprises all dates people have an official day off. This is simple list like the one below.
The vacations
This list comprises all vacation dates of this and the next year. Below how such a list looks likes based on a table containing vacations for various people. We won’t use a list as formatted below, but so you get the idea.
Instead the function we use in our next step to get a list of vacation days is:
You observe a Nested Sequence. The first generates the virtual index we need to evaluate each item, the second executes based on the outcome of th filter using chaining.
The magic of lists
We first have a look at the vacations and holidays.
As a reminder, the starting point is the communicated last day of any sick period or vacation and we want to have the next working day taking into account the working regime, holidays and already planned vacations.
A small example to show the complexity of the puzzle. One gets sick on Wednesday Oct 12 and is also sick at home on Thursday 13. On Friday, Oct 14, there was already a day off planned (vacation). So far so good. But here it comes. On Saturday 15 the work regime turns from 5 days to 4 days and in this regime the first working day is not a Monday, but a Sunday (to compensate).
The employee is expected on Sunday, how do we calculate this outcome?
First the holidays & vacations
We start creating a list of holidays blended with vacations to filter out all non working dates. Put your seatbelts on, this is a serious trip. I won’t explain the trip in detail since elsewhere (like here) I showed how this approach works (or check the work of Max in the community). In our case we need a list that shows us periods of days off. To get there:
- we define the gaps
- we add the first and last date
- we define the periods
In the above we take the holidays and vacations together and filter on greater to ‘theDate’ to get a list that does not contain holidays before the ‘theDate’. I only understood it at the end of my step up when the initial outcome did not fit my expectations. That is how it goes.
Anyway, the next steps you see below:
Don’t worry if you do not understand this. Simply start typing and copy — paste what I made. Your fingers will understand and do the work for you. Let it go and you will be surprised how much you understand already.
On purpose I wrote the text in the If statement to show the work that needs to be done. This outcome I show in my next blog.
We are half way a foundational solution that not only permits us to have an alternative working scheme than the Workday() function presupposes (like 3 or 4 days a week). We will also have the flexibility to define when an employee works, like on Monday, Thursday and Friday.
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.
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.
