# Overlapping time calculation

#### anshul.malhotra

##### New Member
Hi,

I am looking to calculate the overlapping time for each task instance basis the start and end time for each tech.

Enclosed is the file with data and the manual solution which i have prepared to get the relevant reference to the formula or a VBA code which can validated with column "G" & "H".

Thanks and Regards

#### Attachments

• 10.9 KB Views: 8

#### Hui

##### Excel Ninja
Staff member
Anshul

There is only one record for each task

What are you trying to achieve ?

#### anshul.malhotra

##### New Member
Anshul

There is only one record for each task

What are you trying to achieve ?
Dear Hui,

Thanks for your prompt revert, this is sample data there can be 50 tech and data can of 10000 row,

So doing this for small piece of data is easier manually but it becomes next to impossible when number of record increase.

It might be that one tech is handling 2 or 3 task at same time, for example if we consider that there is only 1 tech - he has done 3 tasks, you can consider this data for a single tech as of now and if we get the solution. I would need your help that how will work on multiple tech data in single spread sheet.

1- 9:08 to 9:11
2- 9:07 to 9:10
3-9:10 to 9:15

Then his total overlapping time become 6 min 9:08 to 9:10 = 2 mins and 9:10 to 9:11 = 1 min total = 2+1 =3 min

I want to calculate overlapping time for each tech, please see the above example.

Thanks,

Anshul

#### Peter Bartholomew

##### Well-Known Member
anshul
I seem to have calculated the values you require but I must emphasise that, as it stands, the calculation is not computationally affordable. By the time you reach 10,000 rows you will be calculating 100,000,000 values (almost entirely zeros) every time you enter data.
To make it manageable, each row needs to be calculated independently (not too difficult) and some decision is required for how many tasks one goes back to find an overlap. Maybe if it is over 100 one may assume the task is dormant and so may be ignored?

#### Attachments

• 19.4 KB Views: 6

#### anshul.malhotra

##### New Member

It seems to be working, but how can I extend this to work at 10000+ record for multiple tech's, as I am trying to extend the ranges but it is not letting me do that.

So maximum task one goes back is, First task of the each day for individual tech. So when the date changes the task count start form start time of first task for each tech.

Enclosed is the larger data which can reach max 15000 rows.

Please let me know if any other details will help.

Also, I would like to understand the logic to enhance my knowledge.

Thanks a lot.

Regards,

Anshul

#### Attachments

• 652.3 KB Views: 3
Last edited:

#### Peter Bartholomew

##### Well-Known Member
Hi anshul

Unfortunately I have run out of time for the moment. The basic approach is brute force in that I compare the timing of every task with every other one; hence the 2D array. The latest version of my workbook calculates the rows of the matrix individually, which may open up a route to more efficient calculation (even the use of SUM rather than MMULT helps understandability).

One thing I would observe is that you have assigned the overlap time from two tasks to the later task. This requires the summation to be performed over rows in the red area of the array but there is no clear-cut start point to find overlapping tasks. If, conversely, you were to assign the overlap to the initial task, then the sort order by start time would provide a far more compact calculation; this requires summation over rows in the blue area, each of which forms a continuous range of numbers.

As you see the overall totals of the blue and red columns are the same but the blue calculation might be many thousands of times faster once the problem reaches the size you have described.

#### Attachments

• 22.5 KB Views: 6

#### Peter Bartholomew

##### Well-Known Member
This version is based upon the table and you should be able to add data.
Since I am now performing the calculation task by task, I have been able to remove the TRANSPOSE functions. I search for the finish time of the current task within the list of start times to determine the range of tasks to include in the calculation for each row.

#### Attachments

• 17.3 KB Views: 5

#### anshul.malhotra

##### New Member
Thanks for your response Peter, I am looking for the initial calculation (Red one) which you have shared.

Thanks a a ton for helping while you are occupied, this is great help.

I am enclosing the larger data file where I am not able to apply the calculation you have shared in Red, as that is the perfect calculation which I was looking for.Sorry for the confusion which made you do the other calculation.

I work on the data which I am enclosing and it comes mix and match you will be easily able to relate. Please check the feasibility of working on the whole data together.

Note for your reference - Maximum task one goes back is, First task of the each day/date for individual tech.When the date changes the task count start form start time of first task for each tech.

Thanks and Regards,

Anshul

#### Attachments

• 268.7 KB Views: 2

#### Peter Bartholomew

##### Well-Known Member
@Anshul

I could attempt to simplify the red calculation in due course. My concern is that I estimate the red calculation to take about 60 times the number of operations. Whilst it might be acceptable to wait a couple of seconds for an update, two minutes would not be a good experience. In fact, the fastest calculation might be achieved by using VBA to sort the data by finish time from recent to old, insert the formula as values and re-sort to the original order of start times from old to new. That is to ensure that the overlapping tasks are consecutive.

One other thing I have noticed is that you calculate the overlap between different techs as well as their individual multi-tasking. Is that your intention?

#### anshul.malhotra

##### New Member
I am ready to wait for couple of minutes while calculation is updated.

So overlapping is calculated on 1 tech at time for each date/day separately, overlapping time between different techs are not calculated.

My intention is to get multi-tasking of single tech.

For example - if you will apply filter on the data and select one tech and a single date you will see that task are in order and for new day the task count starts from 1 again. This happens for each tech for every day/date.

Thanks & Regards,

Anshul

#### NARAYANK991

##### Excel Ninja
Hi Anshul ,

Can you clarify one point ?

One particular task has the following time data :

Start time : 16-04-2018 10:06:27

Finish time : 16-04-2018 12:12:32

Following this task , there are several tasks , which have start time and finish time as follows :

16-04-2018 10:08:17 ............... 16-04-2018 10:20:20

16-04-2018 10:21:32 ............... 16-04-2018 10:37:29

16-04-2018 11:40:39 ............... 16-04-2018 11:46:13

16-04-2018 11:49:02 ............... 16-04-2018 12:03:43

Obviously all these 4 tasks overlap with the first one I have mentioned above.

However , one crucial point is that these 4 tasks are mutually exclusive , without any overlap amongst themselves.

What would happen if they did ?

For example , suppose these 4 tasks had the following start and finish times :

16-04-2018 10:08:17 ............... 16-04-2018 10:20:20

16-04-2018 10:18:32 ............... 16-04-2018 10:47:29

16-04-2018 11:19:39 ............... 16-04-2018 11:46:13

16-04-2018 11:19:02 ............... 16-04-2018 12:03:43

What would be the new total overlap time amongst these 5 tasks ?

Or will such a situation never arise ?

Narayan

#### anshul.malhotra

##### New Member
Hi Narayan,

The situation which you have mention will never arise, As of now the multi-tasking can be performed maximum of 2 task.

But it would be great if a dependent cell is there Multi-Tasking can be defined as 2 or 3 because it will never go beyond 3.

Enclosed is the file for your reference

Sheet Name "2 Tech Manual Data for Ref" - I do sort by Tech Name and then Start time to perform manually the overlapping calculation.

Sheet Name - "Larger Data" - I receive the data in this format, so was looking for help if overlapping calculation can be performed in one go, though the solution( Red Calculation) provided by Peter works very will on single Tech.

Sheet Name - "3 Task Multi-Tasking Example" - where overlapping is occurring between multiple task but limit maximum of 3 not more than that.

Note : This might help -The Dependency is tech name as task is simply the defined as each task for on a day/date.

Thanks and Regards,

Anshul

#### Attachments

• 282.9 KB Views: 4

#### NARAYANK991

##### Excel Ninja
Hi ,

Please confirm that in the tab named Larger Data , the data can be sorted in the following order :

Tech Name
................... Start Time
...................................... Finish Time

Narayan

#### anshul.malhotra

##### New Member
Hi ,

Please confirm that in the tab named Larger Data , the data can be sorted in the following order :

Tech Name
................... Start Time
...................................... Finish Time

Narayan
Yes data can be sorted as below, what I use, don't know what impact will it make on calculation by sorting data with finish time also as I have never tried or done it.

Tech Name
................... Start Time

Note :For your reference, each for each tech has to be treated separately (mutually exclusive).

Thanks for your help on this Narayan.

#### anshul.malhotra

##### New Member

Note :For your reference, each day for each tech has to be treated separately (mutually exclusive).

#### anshul.malhotra

##### New Member
@Anshul

I could attempt to simplify the red calculation in due course. My concern is that I estimate the red calculation to take about 60 times the number of operations. Whilst it might be acceptable to wait a couple of seconds for an update, two minutes would not be a good experience. In fact, the fastest calculation might be achieved by using VBA to sort the data by finish time from recent to old, insert the formula as values and re-sort to the original order of start times from old to new. That is to ensure that the overlapping tasks are consecutive.

One other thing I have noticed is that you calculate the overlap between different techs as well as their individual multi-tasking. Is that your intention?
Hi Peter,

Hope you are doing good, I was curious to know if you had time have look on the larger data file. Though Narayan is also helping on the same.

Regards,

Anshul

#### Peter Bartholomew

##### Well-Known Member
@Anshul
I have made some progress but, so far, this only works for a single agent. What I need is a fast calculation to determine the first row of the dynamic array 'active' to be the first entry for the particular [@tech] and [@date]. This requires a nested approximate search to find the final record of the previous block. An alternative strategy is to build a helper table that contains the record number for the first record of every block. Since that would be a one-off calculation an exact match would be affordable.

From what you show the calculation need never run over the entire 15,000 rows so should remain viable.

@NARAYANK991
Raises some good points. As it stands, any period with 3 active tasks gets counted three times over.

#### Attachments

• 40.5 KB Views: 3

#### Peter Bartholomew

##### Well-Known Member
I was curious to know if you had time have look on the larger data file.
Hi Anshul,

I have attached a solution to your problem. It uses Power Query to analyse the block structure of your data so it may well be overkill. Still, it is often easier to start with a working solution and adapt it to something you are comfortable with than to start with a blank workbook!

You may also have problems with the use of named formulas; I know, in reverse, I struggle decoding direct references!

#### Attachments

• 50.5 KB Views: 3

#### vletm

##### Excel Ninja
anshul.malhotra
Someway interesting case ... gotta ask ... in #12 You wrote:
The situation which you have mention will never arise, As of now the multi-tasking can be performed maximum of 2 task.
But it would be great if a dependent cell is there Multi-Tasking can be defined as 2 or 3 because it will never go beyond 3.

Do that match with You sentences?
Or do it matter?
You would like to know Tech 1's overlaps
(if eg 4 tasks in same time then 4 times that overlapped time)
Just interesting to know ...

#### Peter Bartholomew

##### Well-Known Member
@vletm

I like the chart, it captures the nature of the problem well. Am I right, though, that it includes tasks assigned to multiple technicians? If so, we need some colour to identify the technician or a prior sort to order the data first by technician and only then by date/time.

If it is data for one technician then there are several points at which 4 or more concurrent tasks are present.

#### anshul.malhotra

##### New Member
@vletm

Thanks for looking in.

The data you have considered for all the tech I can see that from the graph which you have shared.

"The situation which you have mention will never arise, As of now the multi-tasking can be performed maximum of 2 task.
But it would be great if a dependent cell is there Multi-Tasking can be defined as 2 or 3 because it will never go beyond 3."

In the above statement, I meant at a given time 1 tech cannot handle task more that 2 as of now but might be in future it can be increased to 3 max not more than that.

I have shared a file with example, enclosing the same for your reference.

Please let me know if you have any quires.

@Peter Bartholomew,

Thanks for sharing the file and you are absolutely right in saying it is the way of decoding the formula and comfort level, though the definition you have shared will be helpful. I have yet not used the file but the result you have shown are looking absolutely great.

Just one thing this will work on 2 overlapping task but will not work on 3 overlapping task.

Thanks & Regards,

Anshul

#### vletm

##### Excel Ninja
both:
Above (my sample snapshot) is Tech 1's 03/04/2018 Tasks.
Y-axis has Tasks (which Tech 1 has data)
X-axis has Time (now 03/04/2018)
It's possible to show all Techs all days all Tasks in one view ...
but it have challenge to read it!

anshul.malhotra
In the above statement, I meant at a given time 1 tech cannot handle task more that 2 as of now but might be in future it can be increased to 3 max not more than that.
At a given time (as seen in that sample) there are already more than 2 tasks.
as You could see ...
if there are more than two black bars (y-axis) in same time (x-axis) then
I've used Your file and that is output from that.
Same kind of view can get 'any combination of Tech/Task/Day'.

How would You use those overlapping time values? or
Would someone see where has been 'too much overlapping'?
Where/how do it matter, if overlapping?
Do someone care of 'Daily hours' or 'Daily Task hours'?
Later 'term' would be much greater!
All depends of needs!

Peter Bartholomew
it captures the nature of the problem wellThere could be challenges ... nothing (red word).
Those challenges could solve after see 'what-is-going-on'.
As written in 'both' section.
Same kind of view can get 'any combination of Tech/Task/Day'.
For me, I could see quicker overlaps from graph than 'numbers'.
If focus in smaller ranges as in that sample (Tech 1's 03/04/2018 Tasks.)

#### anshul.malhotra

##### New Member
Sorry I misunderstood the graph, there seems to be some problem in Larger Data Sheet tab, as it can not happen as of now.

Attached is the updated file for your reference.

@vletm

If you simply sum up the length of each task, the time will come out to be more than working hours of an individual for day, so to check what is the efficiency one is working at I am working out this data.

@Peter Bartholomew @vletm @NARAYANK991

Dear All is there a possibility you guys can provide me overlapping start and end time, the way I have calculated it.

#### Attachments

• 853.6 KB Views: 4

#### vletm

##### Excel Ninja
anshul.malhotra
Different data ... can cause ... different results.

... quick look ... still overlaps > 2 and less tasks?

I don't know nor use word simply.

> can provide me overlapping start and end time...
The newest data has 9792 rows ... I didn't check overlaps!
It would be possible to provide ... but I would do it my way.

#### anshul.malhotra

##### New Member
Seems there is some confusion as I have tried at my end doing it manually as well as with the first solution provided Peter.

As per the image you have shared, it looks like that you have not consider the tech name for checking the overlapping.

The motive is to check overlapping time of individual tech task on each day/date, a tech can handle maximum2 tasks which is been proven by the result of Peter's file and what I do manually.

Regards,

Anshul