How to Coda breaks between periods?
when your tasks have overlap and you need periods
In the community I contributed by partly responding to:
I have tasks with start and end time. I would like to have a canvas formula that displays Time left.
How do I subtract that time so that 1 minute of real time can only count as one minute, instead of 2, if for example there are 2 tasks running at the same time?
In this blog I deal with one aspect of this question: getting the periods and their duration uniquely based on start & end time. The translation to the canvas and the countdown I put aside here.
We start simple and look at a period without breaks. To solve this matter we first need to know the total duration of the consecutive tasks. We first create a list of unique time start & end times and we order them. We asks for all items in the list and thus we use thisTable
Based on this list we ask for the duration of each period:
The last step was to take the sum of these intervals. The question was to have the function on the canvas. We need a filter to take away the blanks.
How to deal with breaks?
So far the exercise was not complicated. This work has been documented before in my blogs and in various contributions of other makers in the community.
The additional challenge relates to what you see below.
Task 4 starts at 1:00 PM and so we humans ‘know’ that between 11:00 AM en 1:00 PM there is a break. How to communicate this to the computer? Put differently, what is the assumption we have to be explicit about?
The relevance of this exercise is that you may have a long list of tasks and they are many and / or unsorted and the human mind can no longer see through. In that scenario you’d like to ask Coda to help you out. It becomes even more relevant the moment you are limited in the number of periods and the duration of periods due to legal obligations (like driver time).
Take the example below, it is still partly sorted (for the sake of the example), but it gets already difficult.
Express the implicit assumption
We have a break when the next starting time is later than the last ending time. All we have to do is to get per ‘block’ the next starting time and rest follows.
It appears not to be that simple.
In the following we assume you understand how to Coda already. We start with showing the outcome and next how to get there.
Step 01 — getting the unique slots
The first step is actually the main step. We are looking for breaks, below how that goes. The filter gives back the values that are greater than the end times. We complete the outcome with the first value of the list about starting times, not by using First(), but by applying Min(). It was the great Coda connaisseur Joostmineur who noticed the need for replacing First() by Min() to avoid that after adding a row, you run into problems.
The above results in the list below, also including step02.
To move from step01 to the second step, we take each second value.
Next we look for the tasks we relate to the start & end of each period, that is actually easier than you might expect. We make use of a column named rowIndex, however I could have integrated this function also in the code snippet.
The above code snippet translates into what you see below. The startTask are stept03 and the result of a (again) a filter. What happens next you may not see often, but is rather logical in this context. We make use of chaining. Since we ask for the row value of each task, we have access to all related information living on this row, including the rowIndex. Because this is a number we can use it to get the previous value and these are the last two lines resulting in endTasks.
We don’t have a rowIndex with value zero, that one is skipped. To get the proper outcome we only have to recombine the elements we just developed.
Below one way of presenting the duration related to consecutive activities, thus per period . We wrapped this element together with others into a Format() function to get the outcome we started with.
In this blog we have demonstrated that you can find the periods and calculate the length of each period based on the start & end time. Although the code snippet is not remarkably long, to get it right you need to apply the right assumptions. In our case the first filter is essential and the rowIndex we added simplified the calculation in the last part.
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.
