what should i use

[darryll]

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.

[Myrna Larson]

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"
<[email protected]> 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.

[darryll]

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"
> <[email protected]> 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.
>
>

[Myrna Larson]

Your shifts overlap: 23:30 to 7:30 and 16:00 to midnight. Is that a typo?

For a worksheet formula solution, I am going to assume that a production
period NEVER crosses midnight (the shifts we can deal with).

Let's say you have your shift start and stop times in a table in K1:M2, i.e.
that range looks like this (I've eliminated the overlap in the shifts):

K L M
1 7:30 16:00 23:30
2 16:00 23:30 7:30

You have the production start and stop times for a product in B3 and C3.

This formula will return the number of hours overlap between the production
time and the first shift:

=MAX(MIN(C3+1/48,$K$2+1/48)-MAX(C2+1/48,$K$1+1/48),0)

I add 1/48 (half an hour) to the times so a shift doesn't look like it crosses
midnight (e.g. 23:30-07:30 gets shifted to 0:00 to 8:00).

Then we get (the earlier of the shift end and the production end) and subtract
(the later of the shift start and the production start). If the result is <0,
change it to 0 (the outer MAX formula). The result is the number of hours of
overlap.

NOW... if my first assumption re the production shift never crossing midnight
isn't true, the formulas would be significantly more complicated. I expect
that will be the case. I can't see putting that kind of constraint on
production.

I hate to deal with such "monsters", so I wrote a VBA function that will
handle periods (production or shift) that cross midnight. The code is below.

Once you've put the code in a standard module in your workbook, these 3
formulas will return the number of hours in common between the production
period and each of the 3 shifts, respectively:

=ShiftHours(B3,C3,$K$1,$K$2)
=ShiftHours(B3,C3,$L$1,$L$2)
=ShiftHours(B3,C3,$M$1,$M$2)

If you only want a yes/no answer, make it

=ShiftHours(B3,C3,$K$1,$K$2)>0

The VBA will be slower to recalculate, but (IMO) the formulas are much easier
to deal with <g>. There's always a trade-off.

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Option Explicit

Type PeriodStartStop
Start As Double
Stop_ As Double
End Type

Function ShiftHours(ProdBegin As Date, ProdEnd As Date, _
ShiftStart As Date, ShiftEnd As Date) As Double
Dim i As Long
Dim j As Long
Dim N As Double
Dim Shift() As PeriodStartStop
Dim InProduction() As PeriodStartStop

'if Production hours don't span midnight, there's just one period; if do
'span midnight, will break into 2 periods, before and after midnight
GetPeriods CDbl(ProdBegin), CDbl(ProdEnd), InProduction()

'ditto for the shift
GetPeriods CDbl(ShiftStart), CDbl(ShiftEnd), Shift()

'get overlap of each segment of production period
'with each segment of the shift
N = 0
For i = 0 To UBound(InProduction())
For j = 0 To UBound(Shift())
N = N + GetOverlap(InProduction(i), Shift(j))
Next j
Next i

ShiftHours = Round(N * 24, 5)

End Function

Private Sub GetPeriods(ByVal t1 As Double, ByVal t2 As Double, _
Period() As PeriodStartStop)
'NB: t1 and t2 are time values 0 <= t < 1, not hours

t1 = t1 - Int(t1)
t2 = t2 - Int(t2)

'does period span midnight?
If t1 <= t2 Then
'no: just one period
ReDim Period(0 To 0)
Period(0).Start = t1
Period(0).Stop_ = t2
Else
'yes: split into 2 periods: t1 to midnight, midnight to t2
'note: in 1st period midnight = 1, in 2nd period, midnight = 0
ReDim Period(0 To 1)
Period(0).Start = t1
Period(0).Stop_ = 1
Period(1).Start = 0
Period(1).Stop_ = t2
End If
End Sub

Private Function GetOverlap(Period1 As PeriodStartStop, _
Period2 As PeriodStartStop) As Double
Dim t1 As Double
Dim t2 As Double

'NB: Stop_ must never be less than Start, which is
'ensured by splitting periods that span midnight
'into 2 periods

'overlap, if any, is from later start to earlier stop
'get the later of the start times
If Period1.Start >= Period2.Start Then
t1 = Period1.Start
Else
t1 = Period2.Start
End If

'get the earlier of the stop times
If Period1.Stop_ <= Period2.Stop_ Then
t2 = Period1.Stop_
Else
t2 = Period2.Stop_
End If

'subtract start from stop to get length of interval
'if result is positive, this is length of overlap
'if result = 0, one period starts when the other ends
'if result < 0, the periods don't overlap; result
' is the size of the gap between periods;

'since we are only interested in overlap,
'change negative result to 0

t2 = t2 - t1
If t2 < 0 Then t2 = 0

GetOverlap = t2
End Function

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

On Sat, 5 Mar 2005 19:57:02 -0800, "darryll"
<[email protected]> wrote:

>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"
>> <[email protected]> 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.
>>
>>