# 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.

## 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.

## 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.

## 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’.

--

--

## More from Christiaan Huizer

I use Coda mainly for (HR) planning & (budget) calculations. Follow me to learn how to Coda with numbers. I blog at least once per week.

## Get the Medium app

I use Coda mainly for (HR) planning & (budget) calculations. Follow me to learn how to Coda with numbers. I blog at least once per week.