Excel 2007 : Production cycle time worksheet

**sdamschen** · 05-30-2010, 08:13 PM

I am trying to figure out how to write a formula to figure out production cycle time.
We will be building 8 "widgets" a day.
We will work 10 hours a day.
There is a 20 minute break at 9:20 am.
Production shuts down for lunch 30 minutes for lunch at 12:30.
Production starts at 6:00 am.
Here is what I can do.
Production cycle time = (10*60)-30/8 or 71.25 minutes
If we start at 6:00 am, the first scheduled cycle completion time = TIME(6,0,0+71.25/1440)

My problem is when I am trying to use a logical "IF' statement to account for the 20 minute break or lunch and still calculate the end of each cycle time through the day I receive a number of error messages.
This is the formula I am trying to make work. I have the cell the formula is in, formatted with a "mm,ss" format.
=(IF(D4+G1/1440>9:20,(D4+G1)/24,(D4+G1+15)/1440))
I have included an attachment to help (a picture is worth a thousand words).

Any help will be greatly appreciated.
I have bought a number of books in an attempt to figure this out, and I am still stumped

**ConneXionLost** · 05-30-2010, 09:08 PM

Sorry, my suggestion had an error.

Post deleted.

Cheers,

**Palmetto** · 05-30-2010, 09:21 PM

Haven't tested the formula for correct results, but to overcome the VALUE error in the statement use the TIME function:

EDIT: revised formula to return correct results

=IF(D4+G1/1440>TIME(9,20,0),D4+G1/1440,D4+G1/1440+0.025)

**sdamschen** · 05-30-2010, 11:09 PM

Thanks for the tip Palmetto,
I plugged your suggestion into my spreadsheet and it DID get rid of the VALUE error.
Unfortunately I am getting the incorrect result.
I will keep plugging away however any other tips would be MUCH appreciated.
Thanks again for taking your time to help!!!

**Palmetto** · 05-31-2010, 06:08 AM

Formula revised as follows:

Unnecessary parentheses removed
Order of operations in last argument changed
Corrected value to add the 15-minutes for the break (from "15" to 0.25)

See previous post for the revised formula.

**sdamschen** · 05-31-2010, 11:49 AM

Thank you again Palmetto,
This morning over coffee I think I found the answer.
=IF(D4+G1/1440<TIME(9,20,0),D4+G1/1440,D4+G1/1440+TIME(0,20,0))
Now I need to figure out how to nest a formula to cover the 30 minute lunch at 12:30.