if elseif formula

kod2th3e · Mar 10, 2011

I am trying to create a formula that will calculate the estimated completion time of a piece of product. Heres the layout of the sheet:

C2 is 6:30AM - (Start Time)
C3 is 9:30AM - (1st Break)
C4 is 12:00PM - (2nd Break)
E2 is 0:36:00 - (Cycle Time of Unit)
E3 is 0:10:00 - (Length of 1st break)
E4 is 0:20:00 - (Length of 2nd break)

from cell B10 going down I would like the formula to calculate the estimated completion time. So, B10 would be the start time (6:30 AM) + the Cycle time (0:36:00) = 7:06:00 AM, B11 would be the previous units estimated completion (7:06:00 AM) + the Cycle time (0:36:00) = 7:42:00 AM, and so on... Now the tricky part is if the completion of a unit lands during the 1st break or 2nd break time the estimated completion time would be pushed out by the duration of the break. For example if a unit was slated to be completed at 9:32, it would end up showing a time of 9:42 and if a unit was slated to be completed at 12:19 it would end up showing a time of 12:39.

Any advice/help would be appreciated, thanks for looking.

Ruddles · Mar 10, 2011

Can you confirm that this formula works on a single set of data:-

Code:

=IF(C2+E2< C3,C2+E2,IF(C2+E2+E3< C4,C2+E2+E3,C2+E2+E3+E4))

kod2th3e · Mar 10, 2011

I believe so, I get an answer of 7:06:00 AM

Ruddles · Mar 10, 2011

And would you consider changing the format of your worksheet so the data goes across instead of being in a 2x3 block of cells?

kod2th3e · Mar 10, 2011

Yeah, the layout of start, break, cycle time, break length, etc. is not critical. So, what ever way I can get this to work would be great.

Ruddles · Mar 10, 2011

Set your worksheet up so that one row represents one cycle - this is much easier to handle than having the data for a cycle spread across three rows:-

<TABLE style="WIDTH: 358pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=476 border=0><COLGROUP><COL style="WIDTH: 27pt; mso-width-source: userset; mso-width-alt: 1316" width=36><COL style="WIDTH: 101pt; mso-width-source: userset; mso-width-alt: 4937" width=135><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2560" width=70><COL style="WIDTH: 18pt; mso-width-source: userset; mso-width-alt: 877" width=24><COL style="WIDTH: 64pt; mso-width-source: userset; mso-width-alt: 3108" width=85><COL style="WIDTH: 95pt; mso-width-source: userset; mso-width-alt: 4608" width=126><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 27pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=36 height=17>

</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 101pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #dbe5f1" width=135>

A

</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 53pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #dbe5f1" width=70>

B

</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 18pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #dbe5f1" width=24>

C

</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 64pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #dbe5f1" width=85>

D

</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 95pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #dbe5f1" width=126>

E

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl67 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: #dbe5f1" align=right height=17>

1

</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">

</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">

</TD><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">

Start Time

</TD><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">

Completion Time

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl67 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: #dbe5f1" align=right height=17>

2

</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">

Cycle Time of Unit