Overlapping time calculation

[anshul.malhotra]

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

 

[Hui]

Anshul

There is only one record for each task
So your technique is correct?

What are you trying to achieve ?

 

[anshul.malhotra]

Anshul

There is only one record for each task
So your technique is correct?

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]

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?

 

[anshul.malhotra]

Thanks Peter for your help,

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

 

[Peter Bartholomew]

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.

 

[Peter Bartholomew]

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.

 

[anshul.malhotra]

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

 

[Peter Bartholomew]

@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]

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]

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]

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

 

[NARAYANK991]

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]

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]

Please read the note as below

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

 

[anshul.malhotra]

@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]

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

 

[Peter Bartholomew]

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!

 

[vletm]

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.

Above is Tech 1's 03/04/2018 Tasks. (Bars shows Length of Tasks)
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]

@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]

@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]

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
there is already more than Your named 'future'.
I've used Your file and that is output from that.
Same kind of view can get 'any combination of Tech/Task/Day'.

About 'overlapping',
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!
If 'many tasks' same time, can Tech focus to each Task?
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]

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.

 

[vletm]

anshul.malhotra
Different data ... can cause ... different results.

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

About my questions: 'Simply sum...' as below for Task1 and days?

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]

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