How to Coda the next working day?
Replacing part II of Workday() — part 02 & final part
In our previous blog we ended in the situation that we understood that the next day was part of some days off (or only one day off) or not. We used the screenshot below to show the path to follow.
The next day
In our If statement we add one day to theDate standing for the next day via Sum(thisRow.theDate,1) Often we write + 1, that reads easier but does not allow for chaining the way I like to do it. Nevertheless, I guess many others would have written the below and that is fine as well:
In my opinion, the equal to symbol “ = ” often blocks our imagination, while the Contains() function permits us to check on more than one alternative. Inside the contains function we have lists separated by a comma representing the Or function. Long story short, we have a check on the next day and up to you how you do it 😜.
Defining the Next date
Since we work with regimes and we already have a way to define the regime, we need to do this for the Next Date, whatever this next date is. In our example Wednesday & Thursday the employee was on sick leave, while Friday was an already planned vacation.
The first day after the days off is the Saturday 15 and this one is part of the regime that indicates the employee works 4 days a week, but is free on Saturday. See below.
How do we know that Saturday is a day off and Sunday is a working day?
Well, the above function outputs a date, always, be it the next date after a period of days off or the day after the sick day or last vacation day.
What we do is the following: we use the If statement to get a date, this date we evaluate as our Next Date.
Evaluation of the Next Date
The task ahead is first to define the regime we are part of. We previously developed the logic we copy — paste and off we go.
In our example Oct 15 is indeed part of the regime that defines that the employee is expected to work 4 days a week starting on Sunday.
To understand the days off and the working days in this schedule we filter on the weekday numbers. We replace in the above ‘regime’ with the working days and chain it to the weekday number. I prefer checking numbers over text, thus weekday number one instead of Sunday because you can sort them easy and you can calculate with them (+ 1, — 1 etc.).
We first check if the next day is working day and if so, we take this day. May it be the case — like in our example that it is not a working day we are on the outlook for the first possible workday. Here you need to make a conceptual leap to turn the weekday logic into a date logic and it are the dates we evaluate on their weekday property (this is a number as you know). Below how it looks.
In the first part of the If statement we ask if the weekday property of the TheNextDate coincides with a working day. May that be the case we turn the date as number value into a date format and we are good to go.
In case it appears not to be a working day, we evaluate the next 7 dates on their weekday property and we take the first. This number is also converted into a date.
Both rules result in the next working day, no matter how the workdays are distributed over the week and no matter where you are in the year.
Here we are, we finally have a solution that took me quite some time to work out and made the first blog on the matter a kind of obsolete. This contribution is refined and complex, but once you have it, you can template it. It is not that I have all the time in the world to work out these solutions. It is very much that we need to respond to practical needs that are not met by the standard Coda functions. I really hope Coda is reading this and turn my suggestion into a more dynamic Weekday() function so everybody with extensive training in Coda can set up complex plannings.
The doc demonstrating this approach you find below. It is not available (not even if you pay me). Based on my contributions in my blogs you can set up the calculation yourself. That works better than any copy — paste logic.
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.
