Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 4
Default 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   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 245
Default 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   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 4
Default 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   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 245
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Stop time - start time calculation squack21 Excel Worksheet Functions 5 December 10th 07 04:20 PM
Time calculation (Subraction of Idle Time) Ajay Excel Discussion (Misc queries) 6 March 4th 07 12:54 PM
Time Calculation wnfisba Excel Discussion (Misc queries) 5 October 18th 06 01:06 PM
Ignoring Time in a Date Time Calculation nmp Excel Worksheet Functions 3 November 23rd 05 09:32 PM
time calculation with military time Ron Thetford Excel Worksheet Functions 8 July 29th 05 07:24 PM


All times are GMT +1. The time now is 11:32 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"