MAX and MIN Simultaneous Room Coverage Times Per Day

[laneglobal]

Please help. Looking for an equation that will calculate the Max concurrent rooms in column D between each start and stop date/time. Column D has what the correct answer would be. If possible would also like the MIN in column E which also has the correct answer. Correct answers are included to help check your work.

Thanks

 

[CyrusTheVirus]

so, for row 1, what does the 4 actually represent and what does the 1 actually represent? can you please help clarify?

 

[laneglobal]

The 4 represents the date/time range overlapped with 3 other date/time ranges in the list. Which tells us 4 things were going on at once at least at some point during the date/time range. Column A is probably not really necessary to take into account because the room has different locations as well. I would just ignore that. Just determine how many simultaneous date/time ranges occurred. Hope you can figure this out this would really help. It's easy to determine manually on a specific day but we have an entire year of data to look at. Please let me know if more clarification is needed.

 

[laneglobal]

The 1 represents the date/time range overlapped with nothing other than it's self for at least some point during the date/time range.

 

[CyrusTheVirus]

then something seems off because if you look at row 5, it starts at 8:55 ends at 14:31, but between that time frame isn't row 2, 3, 4, 6, 7, 8, 9, 10, 11, and 12 all going on as well, wouldn't the max be like 11?

 

[laneglobal]

I see what you mean but when just looking at the start time but when you look at the range (start and end) it's no longer overlapping so the simultaneous overlap is 5.

 

[laneglobal]

We first arrived at 11 too please take a look at the pdf to help visualize...

http://tech-tutoring-tube.pro/Overlap-Time-Visualization-Tool.pdf

 

[laneglobal]

Here's the xls file if that helps...

http://tech-tutoring-tube.pro/Overlap-Tim-Visualization-Tool-DI.xlsx

 

[CyrusTheVirus]

Thanks for the file, i get it now, but i think this one is beyond me, perhaps Aladin or someone else can figure this one out. If something comes to my mind i'll respond, but don't count on it lol, sorry, good luck.

 

[laneglobal]

It's tough right! Pass it on to a guru please.