MAX and MIN Simultaneous Room Coverage Times Per Day

laneglobal

New Member
Joined
Oct 27, 2009
Messages
44
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

Example.png
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

CyrusTheVirus

Well-known Member
Joined
Jan 28, 2015
Messages
737
Office Version
  1. 365
Platform
  1. Windows
so, for row 1, what does the 4 actually represent and what does the 1 actually represent? can you please help clarify?
 

laneglobal

New Member
Joined
Oct 27, 2009
Messages
44
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

New Member
Joined
Oct 27, 2009
Messages
44
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

Well-known Member
Joined
Jan 28, 2015
Messages
737
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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?
 
Last edited:

laneglobal

New Member
Joined
Oct 27, 2009
Messages
44
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.
 

CyrusTheVirus

Well-known Member
Joined
Jan 28, 2015
Messages
737
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,587
Messages
5,637,271
Members
416,963
Latest member
samfuge

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top