How to Coda the next working day?
Replacing part I of Workday()
In the confirmation email I prepared to confirm the communicated sick days by the employee, I added a phrase that we would welcome them back on the next working day after their recovery.
I have to admit I did not immediately recollect the appropriate function to solve this puzzle. Below you see the function Workday() in action. Take the day + 1 and filter out a list of dates. In our case this is the column TheDate in the table DB Holidays.
I assumed that by simply applying this function I would be good to go. But of course, I would not have written this blog may this have been the case. In my context it happens that during several months a year workers also work on Saturday. Unfortunately the WorkDay() function no longer applies.
I had to find a different solution. In this blog I start with recreating the Workday function for the ‘next day’ part that also deals with a longer or shorter work week than Coda assumes. I am not yet dealing with the holidays yet.
Two Steps
First, we use the function IsoWeekday() to get a number per date. This number we need to calculate if the next date is a working day or a day to skip.
The above function resembles the code snippet we discussed earlier this summer. The slider has two options 4 or 5. The why you read below.
The IsoWeekDay() number for Thursday is 4 and it is 5 for Friday. In case the weekday nummer is 4 or less and we have a normal working week and we add one. On a Friday, Saturday or Sunday (5, 6, 7) we apply the second argument.
- 8 minus 5 = 3 (for Friday)
- 8 minus 6 = 2 (for Saturday)
- 8 minus 7 = 1 (for Sunday)
All these outcomes result in a proper plus one since we add the outcome to the date.
May it be that we have a working week of 6 days, then we use the number 5, thus the Friday. The logic remains the same in case you enjoy a 4 day working week ending on Thursday. In this scenario we use the 3 for obvious reasons. Needless to say that all these numbers are IsoWeekDay() numbers minus one.
Step 02
In an easy world an employee works always 5 days or 6 days a week. In our scenario some weeks it is 5 in others it is 6, it depends on the kind of work. In our planning, we schedule employees like below.
All we have to do is to check in which period the last day of sick leave falls. That we do with a standard function.
What rests is an integration of this outcome in the previous developed code snippet. We subtract one from the outcome and give it a name. This value we inject. Below for a starter the name is a column. This keeps the function easy to understand, you see step by step what happens.
In the one below we integrated the outcome using WithName(). This approach I follow when I have to apply this logic in various documents and I need to copy — paste the formula.
All this work so far we only did because the Workday() function offered only takes care of ‘normal’ ´working days and thus excludes the Saturday and the Sunday. Coda may want to consider to update this function to make it a bit more flexible and useful in different cultural contexts.
In my next blog I’ll dive into an alternative for the second part of Workday() that permits us to exclude holidays.
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.
