• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Overlapping time calculation

anshul.malhotra
As per the image you have shared, it looks like that you have not consider the tech name for checking the overlapping.I can see that there are overlappings (more than should >2 ).
And if there are more than should be ... somewhere is something unwanted.
If as there are 'too many' .. it would 'nice to know'.
What to do for those tasks?
Before start to make any calculations, I would like to be sure what needs.
I would not do those manually.
 
The problem is that every pair of tasks have the potential to have an overlap. In writing a single value you have selected which other task is of interest to you.

Each time you combine two tasks there are two further intervals that may be of interest. One is the overall active period and the other is the overlap during which the tech is multi-tasking.

When, in turn, those time intervals are combined with a third, any overlap between the third task and the multi-tasking interval produces a smaller interval in which the tech is 3-way multi-tasking (with two customer lines on hold).
 
Peter Bartholomew
It's not (the p...) or even challenge.
If those has happen as data ... then those would find.
Just check data as needed.
There can be 'any number of overlaps' .. that happens!
With graph, can see, and then those could also write with values.
 
vletm said:
anshul.malhotra
As per the image you have shared, it looks like that you have not consider the tech name for checking the overlapping.I can see that there are overlappings (more than should >2 ).
And if there are more than should be ... somewhere is something unwanted.
If as there are 'too many' .. it would 'nice to know'.
What to do for those tasks?
Before start to make any calculations, I would like to be sure what needs.
I would not do those manually.
Click to expand...

Hi @vletm ,

I think the below information will help, this will

Data.png

So for every overlap time for each tech for each overlapping task has to be defined separately, this there in the file at sheet tab "3 Task Multi-Tasking Example"

The overlapping time of any activity more than 2 can not be clubbed it has be shown separately, for example Overlapping Time 2nd task in column H and overlapping Time 3rd Task in Column L then so on if 4 is there. Though four will never happen but it will make it a full proof solution

I hope this will answer your query.

Regards,

Anshul
 
anshul.malhotra ... windy?

Do You still have an image that there are 'only few overlaps'?

I really couldn't use Your layout!

Check My STILL SAMPLE version:
1) Open file
2) Press [ DO IT ]-button
3) You'll see calculation by steps
(because it takes ... some time and just waiting would be boring).
4) After it will be 'DONE'...
5) You could see something as below
> Left-side per Tasks and Right-side per Techs
> Both shows dates and its Start & Finish times
>> Right-side of those, there are
>> | number of overlaps per day |
>> from every overlap, start and finish times (hh:mm) and overlap time (hh:mm)
Screen Shot 2018-05-22 at 11.03.34.png
6) There is also that graph (more right side).
With that, You could compare those values.
I haven't verified all calculations -
There could be some unwanted results ...
That's why there are some settings.
This is not ready!

> Ideas ... questions?
 

Attachments

  • Overlapping time calculation Required_Larger Data.xlsb
    549.9 KB · Views: 7
Something different. I pretty much ignored the tasks themselves and simply built a list of events (task start or task finish) in time order using Power Query.
Then I added calculation columns to accumulate the number of tasks on the go at any time and determine for how long that condition held. The results are summarise as a table (could be a pivot table but I actually built it with a formula)

This calculation has no limits in terms of the number of concurrent tasks it can handle though perhaps the tech would not see it that way!
 

Attachments

  • Overlapping time calculation Larger Data - PQ.xlsx
    95.5 KB · Views: 5
vletm said:
anshul.malhotra ... windy?

Do You still have an image that there are 'only few overlaps'?

I really couldn't use Your layout!

Check My STILL SAMPLE version:
1) Open file
2) Press [ DO IT ]-button
3) You'll see calculation by steps
(because it takes ... some time and just waiting would be boring).
4) After it will be 'DONE'...
5) You could see something as below
> Left-side per Tasks and Right-side per Techs
> Both shows dates and its Start & Finish times
>> Right-side of those, there are
>> | number of overlaps per day |
>> from every overlap, start and finish times (hh:mm) and overlap time (hh:mm)
View attachment 52349
6) There is also that graph (more right side).
With that, You could compare those values.
I haven't verified all calculations -
There could be some unwanted results ...
That's why there are some settings.
This is not ready!

> Ideas ... questions?
Click to expand...


The calculation you have shared is not matching the expectation/result, In first go what @Peter Bartholomew did and the previous file which he has shared are the result and calculation which I am looking for - As stated earlier also each Tech has to treated separately for each day/date.

Task is a count only for number of activities handled in specific day which can be measured/ checked through the start time count.

Thanks for looking in..

Regards,

Anshul
 
Peter Bartholomew said:
Something different. I pretty much ignored the tasks themselves and simply built a list of events (task start or task finish) in time order using Power Query.
Then I added calculation columns to accumulate the number of tasks on the go at any time and determine for how long that condition held. The results are summarise as a table (could be a pivot table but I actually built it with a formula)

This calculation has no limits in terms of the number of concurrent tasks it can handle though perhaps the tech would not see it that way!
Click to expand...

Thank for sharing the new calculation, the file which you have shared earlier is solving the purpose as of now. Thanks again for your great help.

It would be great if you can look for a calculation which can work on 3 concurrent task at Tech level.

Though I am demanding much I know but if can get the overlapping start and end time it would be really helpful to make someone understand as shared in the file which I have uploaded.

Regards,

Anshul
 
anshul.malhotra
The calculation you have shared is not matching the expectation/result.
Okay As with #23:
Dear All is there a possibility you guys can provide me overlapping start and end time, the way I have calculated it.
Those (overlapping start and end times) are including there.
Which You're looking for.

PowerQuery: I cannot use - so that's it!

As stated earlier also each Tech has to treated separately for each day/date.
There are BOTH, per Task and per Tech.

Task is a count only for number of activities handled in specific day which can be measured/ checked through the start time count.
Really? That 'my value' means something different as You skipped from my text.

Maybe You skipped few things...
 

My Friend,

To be honest it is not that simplified that one can understand.

All times by tech is mentioned in one single row,it would have been great, if it would have been in the format which I have shared. So that working on the data becomes easy not complicated.

1. If overall overlapping time for one tech has to be calculated it is not easy since it is in text format

2. The time format is limited to minutes only, if in case I convert the data to required calculateble format then too overall overlapping time because of missing seconds will not come correct.

I hope you are understanding the concern.
Regards,

Anshul
 
anshul.malhotra
What is something for someone would be totally different for others.

If, as there are, many overlaps per row ... what would be great layout for You?
Make a sample layout that one row's data cause 5 overlaps
and
BOTH, Task and Tech, overlaps would see in SAME Your layout!

Have You mentioned that You would like to other that those times - not what would You do with those ... except something manually.
Haven't You asked to provide times - not which format?

I can add there seconds too - not even challenge!

As many times ... You would also tell what next!
If not information, then output would be something else that You have in Your mind. I cannot know. I won't even guess!

#30 Reply ... This is not ready! .. that means ... not ready!
because, I could do as near as You have written or is possible.

I have ... or had ideas how to show 'better' those overlaps...
 
Please find enclosed sample for your reference, also you can share your thoughts of presenting the data.

Also, please note tech calculation is required for each day/date, task is just counter. I think you are checking the overlapping on task level that is the reason it showing more than 3, which is not the case and requirement.

This is the first time ever i have posted for help, so was under impression as the other Ninjas replied in similar format which uploaded, so I thought on same lines.

Let me know if you require any further clarification.

No offence :cool:, I really appreciate the help and effort you are providing.

Regards,

Anshul
 

Attachments

  • Sample File.xlsx
    9.4 KB · Views: 4
Last edited:
anshul.malhotra
I tried to as basic thing:
Make a sample layout that one row's data cause 5 overlaps
and
BOTH, Task and Tech, overlaps would see in SAME Your layout!
Did I get?

I make those questions or wishes to help You - not me!
Without proper reply to ... it's challenge .. not help You.
I would like to get answers, which would help You.

Could I get the most correct needed results for
a) Tech 1's 03/04/2018
b) Task 1's 03/04/2018

case Similar format ...
Do You would like to get some format or something which would help You?
Of course, it could be in same format ...

I changed something,
but even for me those are not useful,
but You have asked values - not vision .. clear result.
(Black base row, Blue makes overlap & Red shows overlap)
 

Attachments

  • Overlapping time calculation Required_Larger Data.xlsb
    655.3 KB · Views: 7
@anshul.malhotra
Rather than sampling at discrete time intervals as I believe @vletm is doing I have tried processing events (new task or task closure) in time order.
The black task bars are finish times with start times superposed to leave the task duration visible. The red lines are two superposed charts that show the accumulated task count from the following sheet.

The challenge is to do this without hacking the input data too severely.
 

Attachments

  • Sample File (1).xlsx
    21.5 KB · Views: 3
Thanks @vletm for working this out, here is what I have understood.

I have tried using different set of data to validate the results you have populated in the OL_TECH sheet, there seems to be I am missing something out, it is not showing the results.

Please find enclosed sheet for your reference.
 

Attachments

  • Overlapping time calculation Required_Larger Data (2).xlsb
    608.3 KB · Views: 3
anshul.malhotra
I have deleted the data and entered new data but it is not giving result, I have followed the steps.
Question: Which part of my steps include 'deleting'?
... Which You wrote that You have followed ...
... Would You give that number from below?
Check My STILL SAMPLE version:
1) Open file
2) Press [ DO IT ]-button
3) You'll see calculation by steps
(because it takes ... some time and just waiting would be boring).
4) After it will be 'DONE'...
 
Please find the enclosed file with data which is populated, though at a given point of time no tech is handling 3 overlapping task.

I have tried to show the same with red line

I am enclosing the file and graph for your reference.

Also the result in OL_Tech sheet column J K L is the result required against the original data , which I have done through Vlookup and it fulfills the requirement.
Graph.png
 

Attachments

  • Overlapping time calculation Required_Larger Data.xlsb
    891.2 KB · Views: 3
Below are the answers you are looking for;

Do You would like to get some format or something which would help You?

Yes I would, but first you need to make your understanding clear that you might be champion in Excel and VBA but if someone is providing information in a format which working and other are also understanding the same that means there is gap in your assumptions/ understanding.
Which part of my steps include 'deleting'?

If someone has to change the data for testing and old data has to be removed - it means deleting only but later I added the new data and refreshed pivot range it worked.

Let me know if i have not answered any of your other questions because rather than trying to understand logic you are trying to prove what you have done is best.

Thanks for your help...!!!
please excuse the brevity of the message - sent from my phone.
 
Last edited:
I am not sure what is going on with this discussion or whether further input is required? For what it is worth the attached recognises a period of triple tasking (task 16 was changed to give rise to this) and calculates the start and end of the critical period.
 

Attachments

  • Multiplicity - time calculation Larger Data.xlsx
    45.3 KB · Views: 7
You must log in or register to reply here.
Back
Top