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

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

"Heidi" wrote in message
...
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

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

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

"Pete_UK" wrote in message
oups.com...
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 - I can translate it
into Excel formulae. if I can translate it
into Excel formulae.

Hope this helps.

Pete

"Pete_UK" wrote:

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




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,

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

"Biff" wrote:

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

"Heidi" wrote in message
...
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

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

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
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=514668

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$2L2,0,IF(G$2+H$2L2,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:D$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:D$10+E$2:E$10L2,0,IF (G$2:G$10+H$2:H$10L2,L2,G$2:G$10+H$2:H$10)-IF(D$2:D$10+E$2:E$10<K2,K2,D$2:D$10+E$2:E$10)))/(G$2:G$10+H$2:H$10-D$2:D$10-E$2:E$10)*F$2:F$10))

confirmed with CTRL+SHIFT+ENTER


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=514668