Overlapping time intervals

[Scoops]

Hi All

I have three jobs that a piece of equipment can perform: one to put
away, and two to pull out.

Each job has one start time and one end time and the three jobs may
overlap either or both of the other two.

What I would like to determine are discrete answers for:

How many hours is only one job active;
How many hours are two jobs active;
How many hours are three jobs active?

The first answer is the total hours minus the sum of the other two
calculations and I can get the hours for two jobs with:

=IF(OR(Finish1<Start2,Start1>Finish2),0,MIN(Finish1,Finish2)-MAX
(Start1,Start2))

(There is a midnight meridian check in the original formula.)

But what's the formula for all three overlapping?

Thanks for any help.

Regards

Steve

 

[AltaEgo]

Broadly, to get total run time you are looking at the finish time of the
last job - the start time of the first job. To get overlap, you are looking
at the start time of the last job minus the finish time of the first job.
The answer to your question depends how your data are laid out.

Assume the following laid out in the range
A1:H4

Job, Start, Finish, OneJob,TwoJob,ThreeJob, TwoJobOverlap ,ThreeJobOverlap
Job1, stTime, finTime,
Job2, stTime,finTime,
Job3, stTime, finTime,

OneJob calculation in cell D2 is '=C2-B2'
Copy the above down to D4.
TwoJob calculation in E3 is '=MAX($C2:C3)-MIN($B2:B3)'
Copy the above down to E4
ThreeJob calculation is =MAX(C2:C4)-MIN(B2:B4)
(no copy down)
TwoJob Overlap calculation in G3 is '=IF(C2>B3,C2-B3,0)
Copy down to G4
ThreeJobOverlap calculation in H4 is '=IF(C2<B4,0,C2-B4)'


The usual practice working with time that spans midnight is to use a formula
that adds one day (1) to the time after midnight. Example:

=IF(A2<B2,(1+C2)-B2,C2-B2)

My view is the above will become overly complex working with overlap times.
The question to resolve would be how to ensure a formula knows if midnight
comes into calculation. You would need to start thinking about a normal job
duration. For example, if it is reasonable to expect a job lasts less than
12 hours.

If (B2<.5,this way, that way). But, what if two jobs start and finish after
midnight and job duration is small?

My recommendation is you use combined date time entry in cells to get around
this. =Finish-Start always works this way provided data are entered. The
Overlap calculation is easier. The above set of formula does not need to
take midnight cross-over into account (which they now do not).

Chip Pearson has some good information that may help add to you knowledge of
time (date) storage and calculation in Excel :

http://www.cpearson.com/excel/datetime.htm

http://www.cpearson.com/excel/datearith.htm

Possibly, http://www.cpearson.com/excel/overtime.htm

 

[Scoops]

Broadly, to get total run time you are looking at the finish time of the
last job - the start time of the first job. To get overlap, you are looking
at the start time of the last job minus the finish time of the first job.
The  answer to your question  depends how your data are laid out.

Assume the following laid out in the range
A1:H4

Job, Start, Finish, OneJob,TwoJob,ThreeJob, TwoJobOverlap ,ThreeJobOverlap
Job1, stTime, finTime,
Job2, stTime,finTime,
Job3, stTime, finTime,

OneJob calculation in cell D2 is '=C2-B2'
Copy the above down to D4.
TwoJob calculation in E3 is '=MAX($C2:C3)-MIN($B2:B3)'
Copy the above down to E4
ThreeJob calculation is =MAX(C2:C4)-MIN(B2:B4)
(no copy down)
TwoJob Overlap calculation in G3 is '=IF(C2>B3,C2-B3,0)
Copy down to G4
ThreeJobOverlap calculation in H4 is '=IF(C2<B4,0,C2-B4)'

The usual practice working with time that spans midnight is to use a formula
that adds  one day (1) to the time after midnight. Example:

=IF(A2<B2,(1+C2)-B2,C2-B2)

My view is the above will become overly complex working with overlap times.
The question to resolve would be how to ensure a formula knows if midnight
comes into calculation. You would need to start thinking about a normal job
duration. For example, if it is reasonable to expect a job lasts less than
12 hours.

If (B2<.5,this way, that way). But, what if two jobs start and finish after
midnight and job duration is small?

My recommendation is you use combined date time entry in cells to get around
this.  =Finish-Start always works this way provided data are entered.The
Overlap calculation is easier. The above set of formula does not need to
take midnight cross-over into account (which they now do not).

Chip Pearson has some good information that may help add to you knowledgeof
time (date) storage and calculation in Excel :

http://www.cpearson.com/excel/datetime.htm

http://www.cpearson.com/excel/datearith.htm

Possibly,http://www.cpearson.com/excel/overtime.htm

--
Steve













- Show quoted text -

Thanks for the input Alta but you've calculated the total run times in
each case. I need to know by how much they overlap, e.g.

Start Finish
Job1 04:00 08:00
Job2 02:00 07:00
Job3 06:00 14:00

yields the result:
Job1 is never running by itself, has 3 hours when another job is
running and 1 hour when both of the other two are running.
Job2 runs 2 hours alone, 2 hours with one other job and 1 hour with
both.
Job3 runs 6, 1 and 1 for the same categories. (All three jobs will
have the same value for a three job overlap by default.) In table
form that's:

Start Finish 100% 50% 33%
Job1 04:00 08:00 0 3 1
Job2 02:00 07:00 2 2 1
Job3 06:00 14:00 6 1 1

There is no date in the value but there could be a midnight meridian
as this is across a shift pattern so I'll be going the more complex
route for the formulae; I'll use 'Finish1+(Finish1<Start1)'.

Thanks again, any more input would be appreciated.

Steve

 

[AltaEgo]

If you read my message again, you will see column headings TwoJobOverlap
and ThreeJobOverlap together with calculations. However, my results were in
hours rather than you 100%, 50%, 33% format which was not specified in your
original question {not to be taken as criticism}.

In my answer, I made the presumption that machine start-up was in
chronological order (i.e. machine 1 started followed by 2 then 3). If you go
back and copy formula to a test sheet you should get the general idea. To
take into account machines can start in any order, you will need to make
additional calculations to take into account machine 3 to machine 1
overlap. Leaping ahead of myself, if you use the helper column suggested
below, overlap becomes:

The minimum of any two machines AdjFinish times - the Maximum of the same
two machines start times
The three machine overlap is:

Min( three machine AdjFinish) - Max(three machine adjstart)

The latter occurs only once for all machines.


Moving back back, the problem of overlap is difficult when spanning
midnight. You could end up with a very complex set of If()'s. I suggest you
cheat your way around the midnight problem using a helper column. Example:

StartTime, FinishTime, AdjFinish, Duration
18:00, 02:00, =IF(A2>B2,1+B2,B2), =C2-A2
06:00, 14:00, =IF(A3>B3,1+B3,B3), =C3-A3

In the case of row 2 above, AdjFinish will be 26:00 and duration will be
8:00
In the case of row 3 above, AdjFinish will be 14:00 and duration will be
8:00

Hide AdjFinish but use it in place of your FinishTime in all calculations

Format AdjFinish column with a custom format [h]:mm and you will see how it
works.

HTH

 

[Scoops]

If you read my message again, you will see column headings TwoJobOverlap
and ThreeJobOverlap together with calculations. However, my results were in
hours rather than you 100%, 50%, 33% format which was not specified in your
original question {not to be taken as criticism}.

In my answer,  I made the presumption that machine start-up was in
chronological order (i.e. machine 1 started followed by 2 then 3). If yougo
back and copy formula to a test sheet you should get the general idea. To
take into account machines can start in any order, you will need to make
additional calculations to take into account machine 3 to  machine 1
overlap. Leaping ahead of myself, if you use the helper column suggested
below, overlap becomes:

The minimum of any two machines AdjFinish times - the Maximum of the same
two machines start times
The three machine overlap is:

Min( three machine AdjFinish) - Max(three machine adjstart)

The latter occurs only once for all machines.

Moving back back, the problem of overlap is difficult when spanning
midnight. You could end up with a very complex set of If()'s.  I suggest you
cheat your way around the midnight problem using a helper column. Example:

StartTime, FinishTime, AdjFinish, Duration
18:00, 02:00, =IF(A2>B2,1+B2,B2), =C2-A2
06:00, 14:00, =IF(A3>B3,1+B3,B3), =C3-A3

In the case of row 2 above, AdjFinish will be 26:00 and duration will be
8:00
In the case of row 3 above, AdjFinish will be 14:00 and duration will be
8:00

Hide AdjFinish  but use it in place of your FinishTime in all calculations

Format AdjFinish column with a custom format [h]:mm and you will see how it
works.

HTH
--
Steve

Thanks for the input Alta but you've calculated the total run times in
each case.  I need to know by how much they overlap, e.g.

         Start   Finish
Job1   04:00  08:00
Job2   02:00  07:00
Job3   06:00  14:00

yields the result:
Job1 is never running by itself, has 3 hours when another job is
running and 1 hour when both of the other two are running.
Job2 runs 2 hours alone, 2 hours with one other job and 1 hour with
both.
Job3 runs 6, 1 and 1 for the same categories.  (All three jobs will
have the same value for a three job overlap by default.)  In table
form that's:

         Start   Finish   100%   50%   33%
Job1   04:00  08:00        0       3        1
Job2   02:00  07:00        2       2        1
Job3   06:00  14:00        6       1        1

There is no date in the value but there could be a midnight meridian
as this is across a shift pattern so I'll be going the more complex
route for the formulae; I'll use 'Finish1+(Finish1<Start1)'.

Thanks again, any more input would be appreciated.

Steve- Hide quoted text -

- Show quoted text -

Hi Steve

Got it!

But first, apologies for muddying the waters with the 100% etc. They
are actually efficiencies, i.e. if the equipment is doing one job then
it is 100% efficient, with two jobs it works at 50% efficiency and
with three it's 33% efficient. I'm modelling throughput in a
warehouse where the users can select to run multiple jobs at once so I
have to calculate the penalty they pay for making the equipment work
too hard. Anyway...

As the times are based on shift patterns and the night shift starts at
22:00, I've elected to send a pre-midnight start time backwards (with
"=IF(Finish1<Start1,(1-Start1)*-1,Start1)" 22:00 = -0.0833), rather
than send the Finish time forward as convention would ordinarily
dictate.

With that simple change the whole thing fell into place and, with
modifications to your formulae to determine "Job1 AND (Job2 OR Job3)"
for the double overlap and "Job1 AND Job2 AND Job3" for the triple,
it's all working nicely.

Thanks for the assist.

Regards

Steve

 

[AltaEgo]

My pleasure. Good luck with your machines.