Dividing a time span into shifts - overlapping days

[Guest]

I am developing a report that tracks the efficiency of industrial sorters on
an automated assembly line. The line runs around the clock in three shifts
(1: 7:30am to 3:29pm, 2: 3:30pm to 11:29pm, and 3: 11:30pm to 7:29am), and
collects certain performance indicators, such as units processed. I am
trying to calculate the units processed during each shift on each day. For
example, if a run ran from 2:30 pm to 4:30 pm, and processed 100 units, I
would want 50 units credited to shift 2 and 50 units to shift 3 (we are
assuming a steady rate of processing).

My data arrives from the machine with the following columns (each run is a
separate row):

B|D|E|G|H
Run #| Start Date| Start Time| Units Processed | End date | End Time

I have successfully used the start time to determine the starting shift with
IF statements:

=IF(--E3<0.3125,3,(IF(--E3<0.6458,1,(IF(--E3<0.9792,2,3))))) and a similar
formula to calculate the end shift. This works fine if the run only spans
two shifts within the same day.

My problem is that some of the runs, run across more than one shift and even
across days. For example, a run that ran from 10:30 pm on one day to 4:30 pm
on the next, would overlap 5 shifts and two days. I need to be able to
figure out the proportion of time spent on each shift, and use that to get
the proportion of units processed during each shift on each day.

I am currently thinking of a convoluted series of nested IF statements (and
generating lots of columns to the right of my data for each potential shift
within a run), but this seems inelegant and cumbersome. Is there a more
logical way to approach this? Even more specifically, is there a function
that can pair my start/end dates and times with a set of shift start/end
times and calculate the elapsed time for each shift within a run? Something
with MATCH or VLOOKUP maybe?

TIA,
Heidi

 

[Biff]

I don't have a solution but just thought I'd let you know why there aren't
any replies.....

This is extremely complicated. I myself have been trying to do something
very similar for quite some time now and have continually failed. Every
example that I've found also fails! I've found examples that "supposedly"
work but when tested, fail. Every one of them!

The really complex problem is dealing with times that span past midnight
into the next day. For example, a time span from 3:00 PM to 8:00 AM (the
next day).

Good luck!

Biff

 

[Pete_UK]

Actually, I don't think this is too difficult !!

As you say, you can derive the starting shift number from the
start-time and the end shift with the end-time. The elapsed time is
merely the end-date+time minus the start-date+time, and any integer
part of this represents whole days, so all 3 shifts would have 8hrs
times the number of days. That only leaves the fraction of a day
elapsed time. You can work out how much time remains in the starting
shift by subtracting the start time from the shift's finish time
(taking account of any cross-midnight times), so if you take this away
from the partial-day elapsed time it will leave you with a remainder
which may or may not be greater than 8 hours - if it isn't then the
remaining time gets allocated to the following shift. If it is greater
than 8 hours and less than 16, then 8 hours gets allocated to the
following shift, with any remaining time (up to 8 hours) added to the
final shift. If there is anything left (which must be less than 8
hours) it gets added to the starting shift (wrap-around, but less than
24 hours).

That's the theory, anyway - much simpler than a problem I've been
working on to allocate long-duration telephone calls to the appropriate
charging period of varying durations! It's a bit late now here in the
UK, so I'll continue with this tomorrow - see if I can translate it
into Excel formulae.

Hope this helps.

Pete

 

[Biff]

Hi Pete!

The algorithm is pretty straightforward.

see if I can translate it into Excel formulae.

That's the hard part!

How about one single formula, not 5 or 6 !!!!!!!!!

Biff

 

[Guest]

Biff,

Thanks for your support - I think lots of people must do calculations like
this, or at least tear their hair out over calculations like this. I hope we
can find a solution!

Heidi

 

[Guest]

Actually, I don't think this is too difficult !!

Thanks Pete. So far, what you've written is very similar to what I've been
working on. The difficulty comes in actually doing the "allocating" of those
fractional times to specific shifts. At the moment, I am trying to do this
with vlookup and match functions (raw data on one tab, lookup table with each
shift/day combo on another tab). I think my problem is I'm thinking more
linearly, like programming. I want to run through the data one line at a
time and increment a "count" next to each shift by the elapsed time for that
shift based on calculations so far. Getting it to do it all at once in
spreadsheet fashion is proving challenging (to me, at least).

Please do let us know if you make any progress on getting this into excel
formulas, and as Biff said, a simple one-formula answer would be fantastic.
At the moment, I'm spreading out into dozens of columns to the right of my
data to keep track of everything - not very elegant at all...

Heidi

 

[Pete_UK]

The reason I said it was not too difficult is because I have been
trying for a long time to find a simpler way to split durations of
phone calls, as mentioned earlier - the charging periods (similar to
your shifts) are of different lengths (i.e. from 8am to 6pm weekdays -
peak, before 8am and after 6pm weekdays - off-peak, and weekends all
day - yet another charging period). I have managed to do it, like you,
by using many columns to the right, though this is not a very practical
solution when what I really want is the duration split into 3 columns
for the different charges (or shifts in your case).

What I have in mind now is a user-defined function into which you can
pass the start date/time and end date/time, together with a "shift"
parameter of 1, 2 or 3, so that it returns the elapsed time within the
shift selected. I had thought that it might be better to return minutes
rather than Excel date/time formats. I don't know how long it will take
me, but I'll come back here when I've had chance to figure it out.

Pete

 

[daddylonglegs]

Hi Heidi,

I believe I could find a solution for you but just a couple of
questions first...

Doesn't a run from 10:30 pm on one day to 4:30 pm on the next overlap
only 4 shifts?

With such a run I take it you are trying to allocate units to each
specific shift covered (i.e. day 1 early shift, day 1 late shift, day 1
night shift, day 2 early shift etc) rather than just to a shift type,
earlys lates and nights?

 

[daddylonglegs]

Assuming your data like this

Start Date in D2
Start Time in E2
Units Processed in F2
End date in G2
End Time in H2

If you have details of a specific shift like this

Start Date and Time in K2
End Date and Time in L2
Formula in M2

=IF(G$2+H$2<K2,0,IF(D$2+E$2>L2,0,IF(G$2+H$2>L2,L2,G$2+H$2)-IF(D$2+E$2<K2,K2,D$2+E$2)))/(G$2+H$2-D$2-E$2)*F$2

this can be copied down column to apply to other shifts shown in
subsequent rows.

The formula can be adapted into an array formula to allocate units from
multiple runs to shifts, e.g. if you have 9 runs in rows 2 to 10

=SUM(IF(D$2$10+E$2:E$10=G$2:G$10+H$2:H$10,0,IF(G$2:G$10+H$2:H$10<K2,0,IF(D$2$10+E$2:E$10>L2,0,IF(G$2:G$10+H$2:H$10>L2,L2,G$2:G$10+H$2:H$10)-IF(D$2$10+E$2:E$10<K2,K2,D$2$10+E$2:E$10)))/(G$2:G$10+H$2:H$10-D$2$10-E$2:E$10)*F$2:F$10))

confirmed with CTRL+SHIFT+ENTER

 

[Guest]

Daddylonglegs (great name!),

Thanks for your help.

For your first question, a shift from 10:30 pm to 4:30 pm would be in 5
shifts:

10:30-11:30pm - shift 2, day 1
11:30-mid - shift 3, day 1
mid-7:30 am - shift 3, day 2
7:30am-3:30 pm - shift 1, day 2
3:30-4:30 pm - shift 2, day 2

Thanks for the formula. I have to run right now, but I am very eager to
play with it and see how it works. Thank you!!!

Heidi

 

[Pete_UK]

It's 4 shifts really - 11:30pm to 7:30am (next day) is one complete
shift.

Pete

 

[Guest]

Eventually, I want to look at the data by day, so I need to determine how
many units were processed on Feb 1 during 1, 2 and 3rd shift. The 3rd shift
would include the 7.5 hours at the beginning of the day and the 0.5 hours at
the end of the day.

Calling the 0.5 hr shift at the end of the day "shift 4" would work too...

Thanks!

Heidi

 

[daddylonglegs]

I assumed, as Pete did, that the night shift would just count as one but
from the point of view of the formula(s) I suggested it doesn't really
make any difference how you define the shifts - you just have to
provide the start and end time (and date) of each one and the units
should be allocated accordingly

 

[Pete_UK]

Heidi,

I've managed to put a UDF together which does the necessary splitting
of the duration - here it is, freely commented so you should be able to
follow it through:

Function time_split(start_date, start_time, end_date, end_time, sh)
'Split elapsed time into three shifts
'
On Error GoTo Failed:
'Initialise variables
Dim start(3), shift(3)
Dim i, num_days, time_rem
start(1) = TimeValue("23:30:00")
start(2) = TimeValue("07:30:00")
start(3) = TimeValue("15:30:00")
shift(1) = 0
shift(2) = 0
shift(3) = 0
duration = end_date + end_time - start_date - start_time
'Exit function if negative duration
If duration <= 0 Then time_split = 0: Exit Function
'Allocate 8hrs to each shift for each full-day duration
If duration > 1 Then
num_days = Int(duration)
shift(1) = num_days / 3
shift(2) = num_days / 3
shift(3) = num_days / 3
'Remove full-day duration
duration = duration - num_days
End If
'Determine the starting shift (i)
If start_time < start(2) Then
i = 1
ElseIf start_time < start(3) Then
i = 2
ElseIf start_time < start(1) Then
i = 3
Else
i = 1
End If
'Start splitting the duration between shifts
Do Until duration <= 0
'Evaluate remaining time to start of next shift
time_rem = start(((i + 3) Mod 3) + 1) - start_time
'Adjust remaining time for day wrap-around
If time_rem < 0 Then time_rem = time_rem + 1
'Increase this shift's duration, reduce overall duration
If duration > time_rem Then
shift(i) = shift(i) + time_rem
duration = duration - time_rem
'Get ready for next shift
i = ((i + 3) Mod 3) + 1
start_time = start(i)
Else
shift(i) = shift(i) + duration
duration = 0
End If
Loop
'Return appropriate shift time-split
time_split = shift(sh)
Exit Function
Failed:
time_split = CVErr(xlErrValue)
End Function

I tested it out using the structure you outlined above, and placed this
formula in the following 3 cells, formatted as time 37:30:55 or custom
[h]:mm -

L3: =time_split($D3,$E3,$G3,$H3,1)
M3: =time_split($D3,$E3,$G3,$H3,2)
N3: =time_split($D3,$E3,$G3,$H3,3)

These return the proportion of the elapsed time on row 3 in each of the
shifts, where shift 1 is the one which starts at 11:30pm.

You had said that you wanted to apportion the production units to the
shifts, so I used J3 for this elapsed time formula:

=G3+H3-D3-E3

and in cell O3 I entered this, formatted as number:

=L3/$J3*$F3

where F3 are the units in that production run. This can also be copied
to P3 and Q3.

If you don't want to use all these extra columns you could easily
combine the formulae, like:

L3: =time_split($D3,$E3,$G3,$H3,1)*$F3/($G3+$H3-$D3-$E3)
M3: =time_split($D3,$E3,$G3,$H3,2)*$F3/($G3+$H3-$D3-$E3)
N3: =time_split($D3,$E3,$G3,$H3,3)*$F3/($G3+$H3-$D3-$E3)

and this time you will need to format the cells as number - personally,
I like to see how the components are built up.

I hope you and others can test this out thoroughly to confirm it does
the job - I might resurrect my interest in the telephone call duration
puzzle now !

Hope this helps.

Pete

 

[Guest]

Pete,

Wow, thank you so much for the UDF! I've finally had a chance to sit down
and play with it and it looks like a terrific way to figure this mess out.

For runs that start and end within one day, it is returning the correct
elapsed time(s). For runs that cross days, I think the function will need to
return a day as well, because some of the shifts are for the next day and
shouldn't be added in with a previous shift.

For example:
A|Run#|C|Start Date|Start Time|Units|End Date|End Time|I|J |K|L-Shift
1|M-Shift2|N-Shift3
A 24715 C 02/01/06 22:14 8,217 02/02/06 3:09 02/02/06 3:39:22 0:00:00 1:15:42

30 minutes of Shift 1 (column L) should be credited to Feb 1's shift 1.
3:09 should be credited Feb 2's shift 1.

Hmmmmm....

Thank you so VERY much again. This is great stuff!

Heidi

 

[Guest]

Update - we are thinking of having a "day" start at 11:30pm the previous
night. This would put all of shift 1 on one "day", instead of breaking it up
at midnight.

Not sure how that changes things yet, but it seems to simplify things a bit.

Heidi

Heidi,

I've managed to put a UDF together which does the necessary splitting
of the duration - here it is, freely commented so you should be able to
follow it through:

Function time_split(start_date, start_time, end_date, end_time, sh)
'Split elapsed time into three shifts
'
On Error GoTo Failed:
'Initialise variables
Dim start(3), shift(3)
Dim i, num_days, time_rem
start(1) = TimeValue("23:30:00")
start(2) = TimeValue("07:30:00")
start(3) = TimeValue("15:30:00")
shift(1) = 0
shift(2) = 0
shift(3) = 0
duration = end_date + end_time - start_date - start_time
'Exit function if negative duration
If duration <= 0 Then time_split = 0: Exit Function
'Allocate 8hrs to each shift for each full-day duration
If duration > 1 Then
num_days = Int(duration)
shift(1) = num_days / 3
shift(2) = num_days / 3
shift(3) = num_days / 3
'Remove full-day duration
duration = duration - num_days
End If
'Determine the starting shift (i)
If start_time < start(2) Then
i = 1
ElseIf start_time < start(3) Then
i = 2
ElseIf start_time < start(1) Then
i = 3
Else
i = 1
End If
'Start splitting the duration between shifts
Do Until duration <= 0
'Evaluate remaining time to start of next shift
time_rem = start(((i + 3) Mod 3) + 1) - start_time
'Adjust remaining time for day wrap-around
If time_rem < 0 Then time_rem = time_rem + 1
'Increase this shift's duration, reduce overall duration
If duration > time_rem Then
shift(i) = shift(i) + time_rem
duration = duration - time_rem
'Get ready for next shift
i = ((i + 3) Mod 3) + 1
start_time = start(i)
Else
shift(i) = shift(i) + duration
duration = 0
End If
Loop
'Return appropriate shift time-split
time_split = shift(sh)
Exit Function
Failed:
time_split = CVErr(xlErrValue)
End Function

I tested it out using the structure you outlined above, and placed this
formula in the following 3 cells, formatted as time 37:30:55 or custom
[h]:mm -

L3: =time_split($D3,$E3,$G3,$H3,1)
M3: =time_split($D3,$E3,$G3,$H3,2)
N3: =time_split($D3,$E3,$G3,$H3,3)

These return the proportion of the elapsed time on row 3 in each of the
shifts, where shift 1 is the one which starts at 11:30pm.

You had said that you wanted to apportion the production units to the
shifts, so I used J3 for this elapsed time formula:

=G3+H3-D3-E3

and in cell O3 I entered this, formatted as number:

=L3/$J3*$F3

where F3 are the units in that production run. This can also be copied
to P3 and Q3.

If you don't want to use all these extra columns you could easily
combine the formulae, like:

L3: =time_split($D3,$E3,$G3,$H3,1)*$F3/($G3+$H3-$D3-$E3)
M3: =time_split($D3,$E3,$G3,$H3,2)*$F3/($G3+$H3-$D3-$E3)
N3: =time_split($D3,$E3,$G3,$H3,3)*$F3/($G3+$H3-$D3-$E3)

and this time you will need to format the cells as number - personally,
I like to see how the components are built up.

I hope you and others can test this out thoroughly to confirm it does
the job - I might resurrect my interest in the telephone call duration
puzzle now !

Hope this helps.

Pete

 

[Pete_UK]

Heidi,

thanks for feeding back - I thought you must have gone with one of the
other solutions.

I wasn't sure how you wanted to treat a production run of more than 24
hours - the UDF returns the total time within the three shifts, but it
might be that you don't have such long runs.

I suppose you might have a run which starts at, say, 8pm and continues
into the next day until 4pm. If you really want to split the production
by day then you will have to have two rows of data, one covering 8pm to
11:30pm and the other covering 11:30pm till 4pm, and apportion the
units between this split - the UDF will handle it.

Hope this helps.

Pete

 

[Pete_UK]

I say that because the UDF can only return one value, not two, so you
will have to devise an appropriate way of splitting the raw data.

Pete