Time calculation

[Rawknee]

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

 

[AltaEgo]

Have you considered vlookup tables? One table for start times and one for
finish times so you can order them correctly.

 

[Rawknee]

I will try this. Don't know a whole lot about it, but I will read up on it
and give it a shot. Thanks.

 

[AltaEgo]

Contextures has good information:

http://www.contextures.com/xlFunctions02.html


The main points are:

1) Lookup value must be in the first column of your data table
2) Lookup value needs to be in numeric order down the column


Your steps

You can configure vlookup to either require an exact match or provide the
next lowest value from your table. You will need the latter

Create a table of key times. HINT if you only need to consider after shift
start and one hour after shift, you don't need to include every hour or
minute of the day, just key times when you need to move between 1st, 2nd,
3rd. Cover all bases (first value in table = 0, last value = 1).

Your lookup table will be

Time; Start; Finish


Your formula for start will look something like:

=VLOOKUP(yourCell,yourTableRangeOrNamedRange,2)

Your formula for finish will look something like

=VLOOKUP(yourCell,yourTableRangeOrNamedRange,3)