software

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(--E3lt;0.3125,3,(IF(--E3lt;0.6458,1,(IF(--E3lt;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,
HeidiI 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 quot;supposedlyquot;
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

quot;Heidiquot; gt; wrote in message
...
gt;I am developing a report that tracks the efficiency of industrial sorters
gt;on
gt; an automated assembly line. The line runs around the clock in three
gt; shifts
gt; (1: 7:30am to 3:29pm, 2: 3:30pm to 11:29pm, and 3: 11:30pm to 7:29am), and
gt; collects certain performance indicators, such as units processed. I am
gt; trying to calculate the units processed during each shift on each day.
gt; For
gt; example, if a run ran from 2:30 pm to 4:30 pm, and processed 100 units, I
gt; would want 50 units credited to shift 2 and 50 units to shift 3 (we are
gt; assuming a steady rate of processing).
gt;
gt; My data arrives from the machine with the following columns (each run is a
gt; separate row):
gt;
gt; B|D|E|G|H
gt; Run #| Start Date| Start Time| Units Processed | End date | End Time
gt;
gt; I have successfully used the start time to determine the starting shift
gt; with
gt; IF statements:
gt;
gt; =IF(--E3lt;0.3125,3,(IF(--E3lt;0.6458,1,(IF(--E3lt;0.9792,2,3))))) and a similar
gt; formula to calculate the end shift. This works fine if the run only spans
gt; two shifts within the same day.
gt;
gt; My problem is that some of the runs, run across more than one shift and
gt; even
gt; across days. For example, a run that ran from 10:30 pm on one day to 4:30
gt; pm
gt; on the next, would overlap 5 shifts and two days. I need to be able to
gt; figure out the proportion of time spent on each shift, and use that to get
gt; the proportion of units processed during each shift on each day.
gt;
gt; I am currently thinking of a convoluted series of nested IF statements
gt; (and
gt; generating lots of columns to the right of my data for each potential
gt; shift
gt; within a run), but this seems inelegant and cumbersome. Is there a more
gt; logical way to approach this? Even more specifically, is there a function
gt; that can pair my start/end dates and times with a set of shift start/end
gt; times and calculate the elapsed time for each shift within a run?
gt; Something
gt; with MATCH or VLOOKUP maybe?
gt;
gt; TIA,
gt; Heidi
gt;
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.

PeteHi Pete!

The algorithm is pretty straightforward.

gt;see if I can translate it into Excel formulae.

That's the hard part!

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

Biff

quot;Pete_UKquot; gt; wrote in message oups.com...
gt; Actually, I don't think this is too difficult !!
gt;
gt; As you say, you can derive the starting shift number from the
gt; start-time and the end shift with the end-time. The elapsed time is
gt; merely the end-date time minus the start-date time, and any integer
gt; part of this represents whole days, so all 3 shifts would have 8hrs
gt; times the number of days. That only leaves the fraction of a day
gt; elapsed time. You can work out how much time remains in the starting
gt; shift by subtracting the start time from the shift's finish time
gt; (taking account of any cross-midnight times), so if you take this away
gt; from the partial-day elapsed time it will leave you with a remainder
gt; which may or may not be greater than 8 hours - if it isn't then the
gt; remaining time gets allocated to the following shift. If it is greater
gt; than 8 hours and less than 16, then 8 hours gets allocated to the
gt; following shift, with any remaining time (up to 8 hours) added to the
gt; final shift. If there is anything left (which must be less than 8
gt; hours) it gets added to the starting shift (wrap-around, but less than
gt; 24 hours).
gt;
gt; That's the theory, anyway - much simpler than a problem I've been
gt; working on to allocate long-duration telephone calls to the appropriate
gt; charging period of varying durations! It's a bit late now here in the
gt; UK, so I'll continue with this tomorrow - I can translate it
gt; into Excel formulae. if I can translate it
gt; into Excel formulae.
gt;
gt; Hope this helps.
gt;
gt; Pete
gt;
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

quot;Biffquot; wrote:

gt; I don't have a solution but just thought I'd let you know why there aren't
gt; any replies.....
gt;
gt; This is extremely complicated. I myself have been trying to do something
gt; very similar for quite some time now and have continually failed. Every
gt; example that I've found also fails! I've found examples that quot;supposedlyquot;
gt; work but when tested, fail. Every one of them!
gt;
gt; The really complex problem is dealing with times that span past midnight
gt; into the next day. For example, a time span from 3:00 PM to 8:00 AM (the
gt; next day).
gt;
gt; Good luck!
gt;
gt; Biff
gt;
gt; quot;Heidiquot; gt; wrote in message
gt; ...
gt; gt;I am developing a report that tracks the efficiency of industrial sorters
gt; gt;on
gt; gt; an automated assembly line. The line runs around the clock in three
gt; gt; shifts
gt; gt; (1: 7:30am to 3:29pm, 2: 3:30pm to 11:29pm, and 3: 11:30pm to 7:29am), and
gt; gt; collects certain performance indicators, such as units processed. I am
gt; gt; trying to calculate the units processed during each shift on each day.
gt; gt; For
gt; gt; example, if a run ran from 2:30 pm to 4:30 pm, and processed 100 units, I
gt; gt; would want 50 units credited to shift 2 and 50 units to shift 3 (we are
gt; gt; assuming a steady rate of processing).
gt; gt;
gt; gt; My data arrives from the machine with the following columns (each run is a
gt; gt; separate row):
gt; gt;
gt; gt; B|D|E|G|H
gt; gt; Run #| Start Date| Start Time| Units Processed | End date | End Time
gt; gt;
gt; gt; I have successfully used the start time to determine the starting shift
gt; gt; with
gt; gt; IF statements:
gt; gt;
gt; gt; =IF(--E3lt;0.3125,3,(IF(--E3lt;0.6458,1,(IF(--E3lt;0.9792,2,3))))) and a similar
gt; gt; formula to calculate the end shift. This works fine if the run only spans
gt; gt; two shifts within the same day.
gt; gt;
gt; gt; My problem is that some of the runs, run across more than one shift and
gt; gt; even
gt; gt; across days. For example, a run that ran from 10:30 pm on one day to 4:30
gt; gt; pm
gt; gt; on the next, would overlap 5 shifts and two days. I need to be able to
gt; gt; figure out the proportion of time spent on each shift, and use that to get
gt; gt; the proportion of units processed during each shift on each day.
gt; gt;
gt; gt; I am currently thinking of a convoluted series of nested IF statements
gt; gt; (and
gt; gt; generating lots of columns to the right of my data for each potential
gt; gt; shift
gt; gt; within a run), but this seems inelegant and cumbersome. Is there a more
gt; gt; logical way to approach this? Even more specifically, is there a function
gt; gt; that can pair my start/end dates and times with a set of shift start/end
gt; gt; times and calculate the elapsed time for each shift within a run?
gt; gt; Something
gt; gt; with MATCH or VLOOKUP maybe?
gt; gt;
gt; gt; TIA,
gt; gt; Heidi
gt; gt;
gt;
gt;
gt;



quot;Pete_UKquot; wrote:

gt; 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 quot;allocatingquot; 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 quot;countquot; 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
gt;
gt; As you say, you can derive the starting shift number from the
gt; start-time and the end shift with the end-time. The elapsed time is
gt; merely the end-date time minus the start-date time, and any integer
gt; part of this represents whole days, so all 3 shifts would have 8hrs
gt; times the number of days. That only leaves the fraction of a day
gt; elapsed time. You can work out how much time remains in the starting
gt; shift by subtracting the start time from the shift's finish time
gt; (taking account of any cross-midnight times), so if you take this away
gt; from the partial-day elapsed time it will leave you with a remainder
gt; which may or may not be greater than 8 hours - if it isn't then the
gt; remaining time gets allocated to the following shift. If it is greater
gt; than 8 hours and less than 16, then 8 hours gets allocated to the
gt; following shift, with any remaining time (up to 8 hours) added to the
gt; final shift. If there is anything left (which must be less than 8
gt; hours) it gets added to the starting shift (wrap-around, but less than
gt; 24 hours).
gt;
gt; That's the theory, anyway - much simpler than a problem I've been
gt; working on to allocate long-duration telephone calls to the appropriate
gt; charging period of varying durations! It's a bit late now here in the
gt; UK, so I'll continue with this tomorrow - see if I can translate it
gt; into Excel formulae.
gt;
gt; Hope this helps.
gt;
gt; Pete
gt;
gt;

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 quot;shiftquot;
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: www.excelforum.com/member.php...oamp;userid=30486
View this thread: 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$2lt;K2,0,IF(D$2 E$2gt;L2,0,IF(G$2 H$2gt;L2,L2, G$2 H$2)-IF(D$2 E$2lt;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$10lt;K2,0,IF(D$2$10 E$2:E$10gt;L2,0,IF (G$2:G$10 H$2:H$10gt;L2,L2,G$2:G$10 H$2:H$10)-IF(D$2$10 E$2:E$10lt;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--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: www.excelforum.com/member.php...oamp;userid=30486
View this thread: www.excelforum.com/showthread...hreadid=514668Daddylonglegs (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

quot;daddylonglegsquot; wrote:

gt;
gt; Assuming your data like this
gt;
gt; Start Date in D2
gt; Start Time in E2
gt; Units Processed in F2
gt; End date in G2
gt; End Time in H2
gt;
gt; If you have details of a specific shift like this
gt;
gt; Start Date and Time in K2
gt; End Date and Time in L2
gt; Formula in M2
gt;
gt; =IF(G$2 H$2lt;K2,0,IF(D$2 E$2gt;L2,0,IF(G$2 H$2gt;L2,L2, G$2 H$2)-IF(D$2 E$2lt;K2,K2,D$2 E$2)))/(G$2 H$2-D$2-E$2)*F$2
gt;
gt; this can be copied down column to apply to other shifts shown in
gt; subsequent rows.
gt;
gt; The formula can be adapted into an array formula to allocate units from
gt; multiple runs to shifts, e.g. if you have 9 runs in rows 2 to 10
gt;
gt; =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$10lt;K2,0,IF(D$2$10 E$2:E$10gt;L2,0,IF (G$2:G$10 H$2:H$10gt;L2,L2,G$2:G$10 H$2:H$10)-IF(D$2$10 E$2:E$10lt;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))
gt;
gt; confirmed with CTRL SHIFT ENTER
gt;
gt;
gt; --
gt; daddylonglegs
gt; ------------------------------------------------------------------------
gt; daddylonglegs's Profile: www.excelforum.com/member.php...oamp;userid=30486
gt; View this thread: www.excelforum.com/showthread...hreadid=514668
gt;
gt;