Calculating Idle Time Dependent on Workload

Hi guys,

I'm back again

So I am working on some data for a support centre, and one of the metrics they measure on is time spent doing naff all... :D

I have gotten myself confused with the maths for one particular part...

The maximum workload an employee can have at any one time is 2 tasks. For example;

Employee starts work at 9am, and finishes at 9:30am (oh the dream)

In that time the worker completes 2 tasks which both take him the full 30 minutes. His workload would look like he's done 30 minutes of work, when his concurrent rate of work would be 2, because he has completed 1 hours worth of work in 30 minutes. His idle time would be 0, as he was working at full capacity for the duration of his shift.

The issue then comes if the same worker completes 4 tasks within the 30 minutes. The first two are started at 9am, task 1 takes 10 minutes and task 2 takes 15 minutes, but he doesn't start tasks 3 & 4 until 9:15. Leaving an idle time of 5 minutes (time not working at full capacity). If task 3 takes 10 minutes and task 4 takes 15 minutes, then again there is an additional 5 minutes not working at full capacity from 9:25-9:30, so the total idle time for the day would be 10 minutes. If possible, I would like to be able to calculate this idle time by each individual row, and ideally through power query as a custom column. However if that is not possible, formulas will be just fine

How can I complete the final column using formulae, and is there any additional data I would require in order to acheive this? I've highlighted the column I'm stuck on

If there are additional pieces of information I need based on the figures on the workbook, I would need to calculate them manually, and preferably through power query as all of this data is already run through there before it spits it out in a useable format (any help on creating these additional columns through PQ would be greatly appreciated!)

Hey, let me try to help.

I'll need to understand a little better what you want to see on the "green" column. Try to just write what you want to see on each row.

Just an insight that may help. If we turn time in work units, you have 1800 seconds, you said that you can work in tasks simultaneous, this in 3600 work units.
Can you just subtract work seconds from the work units, and then turn the remaining work units in time?

If not, try to explain why, so we can help in a better way.