Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Time calculation
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 |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Time calculation
Have you considered vlookup tables? One table for start times and one for
finish times so you can order them correctly. -- Steve "Rawknee" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Time calculation
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" wrote: Have you considered vlookup tables? One table for start times and one for finish times so you can order them correctly. -- Steve "Rawknee" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Time calculation
Contextures has good information:
http://www.contextures.com/xlFunctions02.html The main points a 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) -- Steve "Rawknee" wrote in message ... 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" wrote: Have you considered vlookup tables? One table for start times and one for finish times so you can order them correctly. -- Steve "Rawknee" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Stop time - start time calculation | Excel Worksheet Functions | |||
Time calculation (Subraction of Idle Time) | Excel Discussion (Misc queries) | |||
Time Calculation | Excel Discussion (Misc queries) | |||
Ignoring Time in a Date Time Calculation | Excel Worksheet Functions | |||
time calculation with military time | Excel Worksheet Functions |