How to combine the duration of all tasks correctly?

I have tasks with start and end time. I would like to have a canvas formula that displays Time left.
I’ve come up with a way to combine total duration but it doesn’t take into account that some tasks can run during the same time.

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?

hi @Fran_Vidicek ,

it is an interesting question. My contribution is based on what you provided, but I directly have to add that this approach will be insufficient once we bring into play breaks. To deal with that, we need a different set-up. However first the easy version:

the version with breaks requires an approach that permits us to create various sub lists, thus sub lists for certain periods like morning work, afternoon work etc. It is not very difficult, but a bit more complicated

Cheers, Christiaan

1 Like

I’m Assuming this is not possible to create in a canvas formula because I can’t reference current values? :thinking:

well @Fran_Vidicek , it is a bit different, but not very much:

Listcombine(Tasks.[Start time],Tasks.[End time]).Sort().unique().WithName(step01,
  Sequence(1,step01.Count()).ForEach(step01.Nth(CurrentValue+1) - step01.Nth(CurrentValue)))

the major difference appears as of the moment you add a period starting at 14h and you want 11h - 14h to be a break
Cheers, Christiaan

1 Like

But I am unable to Sum() the output for some reason.

hi @Fran_Vidicek , there are blanks (you do not see) in the list and this blocks the ‘sum’ logic, so we filter out on not blank:

Listcombine(Tasks.[Start time],Tasks.[End time]).Sort().unique().WithName(step01,
  Sequence(1,step01.Count()).ForEach(step01.Nth(CurrentValue+1) - step01.Nth(CurrentValue))).Filter(CurrentValue).Sum()

this works. anything else :wink:

1 Like


It seems like it doesn’t take into account the current time.
I should have been more precise from the start… my bad

Would it be possible to alter the formula so that it doesn’t count time in the past?
So if there is a task that started 1 hour in the past goes on for another 1.5 hours in the future, the total duration would = 1.5 hours.

@Fran_Vidicek , most is possible, so far we broke down one barrier (avoiding overlap), the next item is to relate the sum to the remaining ahead. Since you have the max end time, I guess you can figure it out (or somebody else will take it over from here).

Tomorrow I have a look again, cheers, Christiaan

1 Like

I managed to get it to take into account only start and end times that are not in the past.

I also figured out a simpler formula:

Tasks.[End time].Max() - Tasks.[Start time].Min()

And this one that excludes the past:

If(Tasks.[End time].Max()>Now(),
   Tasks.[End time].Max() - Now(),

And now the final issue is to calculate the time between tasks (breaks) and subtract it from the scheduled time (After Now() ofc).

@Christiaan_Huizer , no worries, this is not some pressing issues, I appreciate the help so far.

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.