How To Coda Breaks in a Time Table
Calculating breaks for bus drivers in the public transport
In my previous blog on the matter if one column (list) with time slots is better than two slots, we concluded that in most cases we are very fine with two. The examples in this blog are based two columns: departure and arrival times.
The context
My mission was to create time tables for drivers based on a selected time frame and the lines to drive. The selected days have various attributes like it is a weekday or weekend, it is holiday or even a school holiday. All these variations matter in our planning. To be clear, this is not the distribution of drivers over available lines, that is a different story. We assume that we know the driver per line and all we want to show is the start and end time of a working day. Part of the working day are the breaks. In our context they matter quite a lot for they are sometimes rather long, read multiple hours. A working day stretches from early in the morning until late in the evening, while the factual driving time though substantial, is not close to the length of the working day.
The output of this calculation helps drivers to plan their day better.
Step 0 → relevance of If statement
A service stands for leaving the bus depot and getting back to it. Sometimes we have one or even three per day, most however two. The breaks are periods between these services. A day with one service, has no break.
We start with a simple calculation: the time of each service. The usage of the + Hours(24) is — as you might remember — required to avoid that a line that returns after midnight end up with a reverse outcome.
Step 01 → Filtering
Often you have two services per day. This implies we have to filter on the information that ties schedules together. We get this information by filtering on the lignes (combi of line number, days and period). Please note that dates do not play a role in this set-up. This is a date independent approach.
What you see is that I relate item one in the list Départ to item one in the list Arrivé and the same for all items using the Nth() logic. It is rather straightforward approach once you understand that everything is a list. In case you do not see this it might help to visualise the items with a virtual number — generated via Sequence() — in front of them and each time you look on the same virtual row, see also point 3.
An attentive reader may notice that I did not wrap this into an If statement of the type as explained above. Correct. This is easier to follow for you as reader, but in production this is wrapped to avoid issues.
We have thus the all daily schedules tied up and some days have only one schedule, others two or even three. This brings us to the main question, what about the breaks?
There are multiple ways to solve this issue. In the community there is the Max Method which I like a lot. This is a bit different and I hope that both inspire you to find one that works for you best.
Step 01
The first step is using a filter to bring together schedules for one specific day type. In the example you see the standard school period (I guess your French is well enough to understand this):
Step 02
We combine the arrival and departure times using a ListCombine() and a Sort() to ensure the proper order between sub lists.
Step 03
We apply the logic of a virtual index to align each time slot with a virtual number and we use the Sequence() function to get the job done.
Step 04
The virtual index is applied to subtract the time slots. On purpose you see the final result (the breaks) , but also the column Les Trajets. See next point.
Step 05
You see that we have all the time frames in a day, but we only need the breaks. That is a sub set. We already developed the duration of each schedule per day. This information we bring into play. We can either reference the column with this information or recalculate. We go for the latter. We first name the work so far as AllTimeFramesPerDay. Next we define the Schedules, this is a copy — paste of the previous work.
Step 06
Our last step is to relate all the time frames with the schedules to get the breaks. We use a filter as you see below and we only want the part that are ‘not’.
What I did wat reusing already created work. This goes rather fast once you see the logic. Compared to the Max Method it is less clean. However you can follow what happens and maybe even reproduce and that is what matters most to me.
Last but not least, any planner should be worried seeing the yellow parts.
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.
Not to forget: the Coda Community provides great insights for free once you add a sample doc.
