formula for adding time to time eg 90min to 6.30am

V

Vickijo60

I am designing a spreadsheet for work which will record the Start, Complete
and Pop time for plastic products produced by our factory. I am having
trouble getting the formula correct for it. I need it to be able to
calculate the Complete and Pop times when I enter the product code and it's
cycle time. EG Start 6.30am, Complete +90minutes, and Pop +120 minutes. How
do I write a formula to do this. Also how do I write a formula/name range so
that when I enter the product code it will recognise this and add the
relevant cook and cool times to the sheet.
We will have atleast 35 different product codes and cook and cool times.
I would appreciate any and all help with this. I have a bit better than a
basic knowledge with Excel and my work computer is running Excel '97
 
J

Jerry W. Lewis

Use Excel times, which are stored internally as fractions of a day
6:30 am
will be recognized as a time automatically. Preformat elapsed times
with the custom format
[mm]
for easy entry/interpretability

Jerry
 
D

dcronje

Here's a starting point:

=(('Start_Time'*1440)+('Minutes_Required'))/1440

substitue cell references as required and change the formating of th
destination cell
 
A

Arvi Laanemets

Hi

Create sheet Products, with table
Product; Cook; Cool
and enter your products and cooking/cooling times

Define a named range ProdTbl (you can rename it, it's an exmple):
=OFFSET(Products!$A$2,,,COUNTIF(Products!$A:$A,"<>")-1,3)

You can also define another named range, p.e. Products, and use it as source
for data validation list:
=OFFSET(Products!$A$2,,,COUNTIF(Products!$A:$A,"<>")-1,1)

Create a sheet Production, with table p.e.:
Product, Start, Complete, Pop, Cook, Cool

You can format Product column as data validation list, with source (I assume
you created named range Products):
=Products

Into Start column enter start time in time format, p.e. 9:00

The rest of columns contain formulas. For row 2, with product in A2 and
start time in B2:
Complete=IF($B2="","",$B2+90/(24*60))
Pop=IF($B2="","",$B2+120/(24*60))
Cook=IF($B2="","",VLOOKUP($A2,ProdTbl,2,0))
Cool=IF($B2="","",VLOOKUP($A2,ProdTbl,3,0))

Copy formulas down for some amount of rows

Start with filling Production table!
 
Top