How do I find the Max value out of condition with multple matches

[tallshortguy]

I'm new here so I'm not sure if there's a way I can attach a sample worksheet, which may make it easier to see what I'm having trouble with. Basically I have a list of jobs for a certain day along with the time the job began and the time it ended. Within that day I've made time intervals for every second of that day starting at 7AM and have a formula that finds the number of jobs whose job start to job end time interval overlaps with the time interval I created (this I've called Active). This way I know how many jobs are running concurrently throughout the day. I would like to create a new column that finds for every job what the largest number of jobs is that ran concurrently. So I need to find which time intervals the job overlaps with and find the largest Active values that correspond with those intervals.

For example, say job 1 occurred between 7:00 AM (job start time) and 7:03 AM (job end time). It overlapped with 3 intervals, 7:00 (interval start time)-7:01 (interval end time) AM with an Active of 9, 7:01-7:02 AM with an Active of 12, and 7:02-7:03 AM with an Active of 4. I need a formula that returns the max Active for job 1, which in this example would be 12.

Columns
D: Job start time
E: Jobs end time
F: Interval start time (for example, 7:00 AM)
G: Interval end time (for example, 7:01 AM)
H: Active

I know the logic/condition for finding whether the job's time interval overlaps with a certain interval in the day (overlaps if neither (Job Start >= Interval end) nor (Job End <= Interval start)).

Any help would be greatly appreciated.

 

[tallshortguy]

What I asked for was deisred result which I still do not see...

Sorry about that, forgot to include it

For q4, it's start and end time overlaps with 4 time intervals, with the active counts of 3, 4, 4, and 2. I just want the max, so 4. I don't care which 4 value it returns since they're both equal in numerical value.

 

[Robert Mika]

Sorry about that, forgot to include it

For q4, it's start and end time overlaps with 4 time intervals, with the active counts of 3, 4, 4, and 2. I just want the max, so 4. I don't care which 4 value it returns since they're both equal in numerical value.

I see only one interval for q4...

 

[tallshortguy]

I see only one interval for q4...

q4's job starts at 7:00:03 and runs through 7:00:05 (the job end time). That overlaps with the time intervals of 7:00:02-7:00:03, 7:00:03-7:00:04, 7:00:04-7:00:05, and 7:00:05-7:00:06.