How to Coda dynamic intervals?
Planning based on days and weeks (not months & years)
In my blog on intervals and planning I showed the power of supporting tables that help you to find the next date in n days, weeks, months or years. This approach does not deal with any filtering related to holidays or vacations. It is a straightforward approach ⤵️.
In this blog we we look into a mechanism to integrate intervals in a work planning. We hinted on this usage in our blog on Monthly Sick Leave ⤵️.
Coding the Monthly Sick Leave expressed in hours, departed from implicit assumption that the employee would work every week. Maybe only on Sunday 1 hour or Mon — Fri 8 hours per week, but always at least one day each week.
In this blog we explore a method on how to deal with once every few weeks
Not Every Week
In the example below you see that I permit for selecting the desired interval. It is based on an other table that deals with the planning per employee. The last column — theFirstDate — is of major importance in our set up. We need the this first date to calculate the intervals.
When your period starts at November 1, then the first Monday in that period is November 7 as you can see below, because this Nov 01, 2022 is a Tuesday.
theFirstDate
To get Monday 07 as the first date, we need two steps. First we look for the next date. Second we create a list of dates that takes all the dates until the next date, but not the next date itself to avoid we get — in case of an every week logic — 8 dates. We want te start date to be included and we only need 7 dates. That explains the minus one. The filter seems self explanatory to me and does the rest.
Period
The period is the result of another important table. I wrote about the logic of most recent values in this blog ️️️️⤵️.
In the table Periods we only have the fromDate, no need — as explained previously — to have an until date.
The unique combination of each lookup results in a planning we can use to filter out the days that match the interval per employ, per period. In this blog I focus on the final answer, not on all detailed steps in between.
First we filter on the Weekday(). Does the employee for example work on a Sunday or Monday. Filtering on weekdays generates a lists of matching dates for the periods we look into. This list of dates only is valid in case the interval is put on every week. The challenge is however to bring the interval into play and thus offer options like every 2 weeks or every month.
How to Coda this difficult complexity?
Filtering on the interval
For example we start a period on Jan 09, 2023, which is Monday and we have period dealing with Friday and Saturday, both every two weeks.
The first date for Friday & Saturday is thus this Friday Jan 20, next Saturday, Jan 21 then Friday 27 and Saturday 28 etc.. but not all Fridays & Saturdays count, only once every two weeks and the interval started at Jan 09.
Imagine that our vacation starts at Jan 15 and ends Feb 14, which is a Tuesday. What is then the next working day? Well, in the above we found two days, but since the vacation is about a month, we can expect more dates, indeed in this period we have 4 dates and these are Jan 20, 21 and Feb 3 and 4. How did we Coda this outcome?
Step01
We start with a list of dates and we filter out the holidays.
Step02
We use these dates to get the period per date in this step01 list.
Step02B
Based on the outcome of step02 — see below — we ask for the first dates per day in our period.
Step02C — Eye Candy
This step results in a list of next dates related to the periods we need as starting point for our intervals, but please note that this is Eye Candy only .
Step03
This step shows all dates related to Fridays & Saturdays in the relevant periods based on the Step02 outcome.
Step03B — Eye Candy
We need to find a way to check if the dates found in Step03 are part of the interval and we start with calculating the days in between the first date — we use the logic of Step02C and step03. We have a list of dates and via ForEach() we evaluate each of them and evaluating means in this context that we subtract each next Friday and Saturday with the each first date.
Step04 — the solution
The final step is to check which dates respect the interval we defined in our previous tables, more specifically in the table Intervals.
These intervals we need in our Remainder() function. This often overlooked little gem is our ultimate guide in finding the fitting values. By filtering on the outcome is zero we get dates matching our intervals. There is no need to add * 7 to the divisor in the Remainder() — as may have crossed your mind seen the week logic. Note that we added ToNumber() to get values we can compare.
Is this all there is?
Nope, the code snippets we have seen only cover use cases related to weeks and thus days and this for logical reasons. I did not directly see it, but when I combined the Remainder() logic with the RelatieveDate() I noticed — but unfortunately for me not soon enough — that I wanted the impossible.
For example we start a period on Jan 09, 2023, which is Monday and we are dealing with Friday and Saturday, both every month. The question is which Friday & Saturday we need in the next Month? The first Friday & Saturday after Jan 09 are Friday 13 and Saturday 14.
When we apply RelativeDate() we can add one month and his results in Feb 13 and Feb 14, which are a Monday & Tuesday. Instead we wanted a Friday and Saturday. Should we take Friday 10 and Saturday 11 Feb OR 17 & 18? Do we take the first, thus before which is less than a month or after, which is more than a month? Actually I don’t know.
I show you what can be done by taking the nearest value, it is as arbitrary as is the next or previous. We start with defining the options in case the selection is not a next day or a next week using a double Sequence():
to get the date nearby we create a comparison inside the If() statement:
The great Coda connaisseur Joostmineur was so kind to ask me why I had the double sequence, while one would do the job and he is right. This explains the differences in the code snippets.
Below the results:
In the doc below you see the week logic in action.
When to apply this logic?
There are various use cases that cross my mind. First of all, it permits for any planning that is not strictly weekly and deals with irregular intervals that change over time. Second I see value related to Divorce Planning. Unfortunately I am a divorcee myself and when I first read documents with terms as odd & even weeks, I was a surprised. Most people are not aware of week numbers, but deal with dates. However odd & even are easy to calculate in any spreadsheet, dates are harder, until now. Anyway, any type of resource planning is making a step forward with this logic integrated and applied. Good luck!
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 — Human Resource — 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.
