Hello Myrna,

Thank you for your help.

The shift start times are 23:30 to 07:30
07:30 to 16:00
16:00 to 00:00
I may not have been clear in my question I plan the production of products
though the plant.
More that one product may be run during a single shift.
The spreadsheet shows the start and finish times for each product in two
columns.
I have been asked to modify the spreadsheet to show the labour required for
a shift.
As it could be different products within a shift, I will need to compare
weather the start and finish times are within those shift hours and than
select the largest crew required within the shift to then calaculate the
labour crew required across the whole plant.

regards

Darryll

"Myrna Larson" wrote:

Does a person always start work at the beginning of a shift, and does he
always work exactly a full shift?

If the answers are yes and yes, then all you need to do is check the starting
times against the shift starting times. Let's say the shift starts are 08:00,
16:00, and 0:00.

If the individual starting times are in C2:C100, =COUNTIF(C2:C100,TIME(8,0,0))
will give you the number of people who started at 8:00. Use TIME(16,0,0) to
get those who start at 4:00 PM, and TIME(0,0,0) to get those who start at
midnight.

Or do a person's start and end time not necessarily correspond with a shift?
In that case, do you want a count of, say, the total number of people who
worked some time between 8:00 and 4:00, i.e. those who worked 8-4 plus those
who worked 10-6, plus those who worked 3:30-11:30, with the latter 2 people
being counted on 2 shifts?

If you have the first shift start time in K1 (say 7:00) and end time in K2
(say 15:30), and a person's start and end times in C2 and D2, this formula
will tell you whether the person worked any time during that shift:

=IF(MIN($K$2,$D2)-MAX($K$1,$C2))0,1,0)

It determines the earlier of the shift end and the persons quitting time. From
that it subtracts the later of the shift start and the person's starting time.
If the result is 0, the person worked during that shift, so the formula
returns 1.

But the formula would need to be modified if either the shifts or work periods
span midnight.

On Sat, 5 Mar 2005 16:53:02 -0800, "darryll"
wrote:

Hello and thank you for any help in advance
I have a spreadsheet that is used for planning production, I want to compare
a number of start times in one column and finish times in another column to
be within a shift start and finish time as the factory runs 24 hrs.

Then return from the labour column the corresponing greater number.

This is so i am able to stop manually entering the largest labour number on
the three shifts.