tallshortguy
New Member
- Joined
- Mar 13, 2013
- Messages
- 7
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.
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.
Last edited: