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

How to calculate concurrent or over lapping time events in different dates

H

Hélio

New Member
Hi there!! My name is Hélio and I´m from Brazil.. From here i read a lot of excel tips from Chandoo site and all are amazing.. Now i have a situation that i can´t jump out. I see a lot of posts regarding concurrent and or overlapping event counter but all those posts are keeping the line that all events are running in the same. One most used formula is:::: =SUMPRODUCT((B:B<C2)*(C:C>B2))

obs: Start date is: yyyy/mm/dd

upload_2015-4-30_16-55-2.png


But if i use this formula for different dates this will be crazy because concurrent events into other days will be all put together as it was in the same day..

My question: How to make excel identify different data ranges and calculate automatically the overlapping just inside those ranges?
e.g
upload_2015-4-30_17-2-55.png

My best regards for all
 

Attachments

Hi Narayank, this works.. Your way out is really genial... I was all the time imaging to work using only data set on table and not figured out in use a helper colunn.. Is there a way to put a flag to group the overlaping itens? See the atached file. On day one we find 2 differnt overlaping period. is there a way to set collors or a flag to show us the overlaping sequence?

My best regards and thanks, you are the one...
 

Attachments

Hi ,

Hélio said:
Hi Narayank, this works.. Your way out is really genial... I was all the time imaging to work using only data set on table and not figured out in use a helper colunn.. Is there a way to put a flag to group the overlaping itens? See the atached file. On day one we find 2 differnt overlaping period. is there a way to set collors or a flag to show us the overlaping sequence?

My best regards and thanks, you are the one...
Click to expand...
I think it will be difficult ; I can see that row 2 overlaps with row 3 ; suppose we use Color1 to color rows 2 and 3.

Now row 3 overlaps with both row 2 and row 4 ; what color will we use to color these 3 rows ?

When we come to row 4 , it overlaps with row 3 ; what color will we use to color these 2 rows ?

Can you comment ?

Narayan
 
Hi Narayan, tks for prompt reply.. I said collor but it could be a different flag.. the situation is: each of one line there is one more colunn with bandwith usage. What i need is to sum the band with for the most concurrent group.. Please see atached...
 

Attachments

As i said before.. Genial.... I will apply this to the production table... I will adapt and test it... You are the one... Thaks a lot...

Thaks Chandoo site.. this is the best..
 
You must log in or register to reply here.
Back
Top