Start Time & End Time

P

Pete

I have a table of data for my Scheduled cutting list that is exported
from another program into an Excel Spreadsheet and from this table I
would like to be able to find the start and end time of the production
runs automatically in a separate table.

The table is set up like this

Planned order Time Product Description Volume
12345 08:00:00 Product 1
3500
23435 11:32:00 Product 2 5680
14567 13:24:00 Product 3 10000

and so on.

I can find the start time by using Vlookup( Planned order,Datatable,
2,false) which returns 08:00:00. The end time of Product 1 is the
start time of the next planned order for Product 2 etc etc

What I would like is a table that looks like this:

Planned order Start time End time Volume
12345 08:00:00 11:32:00 3500
23435 11:32:00 13:24:00 5680
14567 13:24:00 22:00:00 10000

I hope I've explained this ok

Regards

Pete
 
N

Nicholas Perkins

Hi Pete,

What you would be best to use is an array formula. When you enter an
array formula, you press Ctrl+Shift+Enter instead of just Enter. (Some
people call them CSE formulas because of this key combo).

I won't go too deep into specifics of how they work as there are much
better people and websites to check out.

The formula you would enter is:

=MIN(IF($H$2:$H$5>H2,$H$2:$H$5,""))

Where Column H has your start times and H2 is the start time of the
current order. After you press Ctrl+Shift+Enter, it will show curly
braces around it:

{=MIN(IF($H$2:$H$5>H2,$H$2:$H$5,""))}

The array formula works by calculating each cell of the array
separately. So the IF statement is like having this instead:

If ($H$2>H2,$H$2,"")
If ($H$3>H2,$H$3,"")
If ($H$4>H2,$H$4,"")
If ($H$5>H2,$H$5,"")

It then takes the result of that IF statement (either the cell value,
or "") and finds the minumum (lowest) number. This is the next time in
the series.

This will work for you only if there is no crossing over to the next
day. That would require storing dates as well as times, but the
formula should still work.

Happy to send you the workbook that I used to work on this if you
would like an example. Drop me an email with:

The USENET Group Name AND the Subject Header in the subject line
Your Email Address
A copy of your original post.

Cheers,
Nick



Then the MIN formula finds the lowest time (earliest) from the
remaining ones.

This is ok if you are only looking at say one machine. If you have
multiple machines then you would need separate tables for each one.
There might be better solutions - anyone?
 
N

Nicholas Perkins

Hi Pete,

What you would be best to use is an array formula. When you enter an
array formula, you press Ctrl+Shift+Enter instead of just Enter. (Some
people call them CSE formulas because of this key combo).

I won't go too deep into specifics of how they work as there are much
better people and websites to check out.

The formula you would enter is:

=MIN(IF($H$2:$H$5>H2,$H$2:$H$5,""))

Where Column H has your start times and H2 is the start time of the
current order. After you press Ctrl+Shift+Enter, it will show curly
braces around it:

{=MIN(IF($H$2:$H$5>H2,$H$2:$H$5,""))}

The array formula works by calculating each cell of the array
separately. So the IF statement is like having this instead:

If ($H$2>H2,$H$2,"")
If ($H$3>H2,$H$3,"")
If ($H$4>H2,$H$4,"")
If ($H$5>H2,$H$5,"")

It then takes the result of that IF statement (either the cell value,
or "") and finds the minumum (lowest) number. This is the next time in
the series.

This will work for you only if there is no crossing over to the next
day. That would require storing dates as well as times, but the
formula should still work.


Happy to send you the workbook that I used to work on this if you
would like an example. Drop me an email with:

The USENET Group Name AND the Subject Header in the subject line
Your Email Address
A copy of your original post.

Cheers,
Nick
 
R

Ron Rosenfeld

I have a table of data for my Scheduled cutting list that is exported
from another program into an Excel Spreadsheet and from this table I
would like to be able to find the start and end time of the production
runs automatically in a separate table.

The table is set up like this

Planned order Time Product Description Volume
12345 08:00:00 Product 1
3500
23435 11:32:00 Product 2 5680
14567 13:24:00 Product 3 10000

and so on.

I can find the start time by using Vlookup( Planned order,Datatable,
2,false) which returns 08:00:00. The end time of Product 1 is the
start time of the next planned order for Product 2 etc etc

What I would like is a table that looks like this:

Planned order Start time End time Volume
12345 08:00:00 11:32:00 3500
23435 11:32:00 13:24:00 5680
14567 13:24:00 22:00:00 10000

I hope I've explained this ok

Regards

Pete
Click to expand...

The "best" formulas depend on whether you are working with Excel 2007+ or
earlier versions. 2007+ has the IFERROR function, and earlier versions do not.

I used NAME'd ranges for the variables. You can either NAME the ranges, or use
cell references.

I don't know how you determine the End Time of the last entry; so in your
example, the formula will return a #NUM! error. This can be altered depending
on your specifics.

There is error checking so as to return blanks for entries that are not
present.

Here are the formulas:

Planned Order:
Excel 2007+:
=IFERROR(INDEX(Planned_order,MATCH(Start_Time,Time,0)),"")

Excel 2003 and earlier:
=IF(ISNA(INDEX(Planned_order,MATCH(Start_Time,Time,0))),
"",INDEX(Planned_order,MATCH(Start_Time,Time,0)))

Start Time:
=IF(COUNT(Time)<ROWS($1:1),"",SMALL(Time,ROWS($1:1)))

End Time:
=IF(COUNT(Time)<ROWS($1:1),"",SMALL(Time,ROWS($1:2)))

Product Description:
Excel 2007+
=IFERROR(INDEX(Product_Description,MATCH(Start_Time,Time,0)),"")

Excel 2003 and earlier:
=IF(ISNA(INDEX(Product_Description,MATCH(Start_Time,Time,0))),
"",INDEX(Product_Description,MATCH(Start_Time,Time,0)))

Volume:
Excel 2007+:
=IFERROR(IF(INDEX(Volume,MATCH(Start_Time,Time,0))=0,
"",INDEX(Volume,MATCH(Start_Time,Time,0))),"")

Excel 2003 and earlier
=IF(Start_Time="","",IF(INDEX(Volume,MATCH(Start_Time,Time,0))=0,
"",INDEX(Volume,MATCH(Start_Time,Time,0))))
--ron
 
R

Ron Rosenfeld

The "best" formulas depend on whether you are working with Excel 2007+ or
earlier versions. 2007+ has the IFERROR function, and earlier versions do not.

I used NAME'd ranges for the variables. You can either NAME the ranges, or use
cell references.

I don't know how you determine the End Time of the last entry; so in your
example, the formula will return a #NUM! error. This can be altered depending
on your specifics.

There is error checking so as to return blanks for entries that are not
present.

Here are the formulas:

Planned Order:
Excel 2007+:
=IFERROR(INDEX(Planned_order,MATCH(Start_Time,Time,0)),"")

Excel 2003 and earlier:
=IF(ISNA(INDEX(Planned_order,MATCH(Start_Time,Time,0))),
"",INDEX(Planned_order,MATCH(Start_Time,Time,0)))

Start Time:
=IF(COUNT(Time)<ROWS($1:1),"",SMALL(Time,ROWS($1:1)))

End Time:
=IF(COUNT(Time)<ROWS($1:1),"",SMALL(Time,ROWS($1:2)))

Product Description:
Excel 2007+
=IFERROR(INDEX(Product_Description,MATCH(Start_Time,Time,0)),"")

Excel 2003 and earlier:
=IF(ISNA(INDEX(Product_Description,MATCH(Start_Time,Time,0))),
"",INDEX(Product_Description,MATCH(Start_Time,Time,0)))

Volume:
Excel 2007+:
=IFERROR(IF(INDEX(Volume,MATCH(Start_Time,Time,0))=0,
"",INDEX(Volume,MATCH(Start_Time,Time,0))),"")

Excel 2003 and earlier
=IF(Start_Time="","",IF(INDEX(Volume,MATCH(Start_Time,Time,0))=0,
"",INDEX(Volume,MATCH(Start_Time,Time,0))))
--ron
Click to expand...

I forgot to write that you enter the above formulas in columns in the first
row; then select them all and copy/drag (fill) down as far as required (or
until you start seeing blanks in the Start_time column).

Also, you will need to format the Start Time and End Time columns as Time.
--ron
 
P

Pete

I forgot to write that you enter the above formulas in columns in the first
row; then select them all and copy/drag (fill) down as far as required (or
until you start seeing blanks in the Start_time column).

Also, you will need to format the Start Time and End Time columns as Time..
--ron- Hide quoted text -

- Show quoted text -
Click to expand...

Thanks Ron, I'll give it a whirl.
 
K

Khusro

Hi Pete try to copy (Cell) start Time of 2nd product & past link to the cell
1st product It looks simple . whenever you will enter the start time of your
product this start time will be the end time of your previous product.

Khusro
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Conditional Time Calculation 10
calculating average handling time 7
Showing 24 hour time along X-axis 0
Determine if night shift by start & end time 2
Subtracting time...again 2
Min, Max 3
I need Time Range to return Value 4
separating date and time 5

Top