I am trying to figure out a production problem that deals with job completion
and shifts.

There are 3 shifts working:
1st shift: 07:30-15:30
2nd shift: 15:30-23:30
3rd shift: 23:30-07:30

A job can be started and completed in 3 possibilities.
1) Starts and finshes on 1 shift = 1 hour before end of a shift.
2) Starts and finishes on 1 shift < 1 hour before end of a shift.
3) Starts on 1 shift and ends on another shift.

I will have a column for job start time and a column for job finish time. I
need a third column to show the following possibilities:


1st shift (job was started 1st shift and completed =1 hour before end of 1st)

1st/2nd shift (job was started 1st shift and completed < 1 hour before end
of 1st)

2nd shift (job was started 2nd shift and completed =1 hour before end of 2nd)

2nd/3rd shift (job was started 2nd shift and completed <1 hour before end of
2nd)

3rd shift (job was started 3rd shift and completed =1 hour before end of 3rd)

3rd/1st shift (job was started 3rd shift and completed <1 hour before end of
3rd)

I am using the formula:
=IF(MOD(b1-a1,1)*1440=60,"1st shift","1st/2nd shift") but i need to take
into consideration all the shifts, plus when the job starts. Does anyone have
advice? Thanks in advance.

Rawknee