time differences in a column

[68magnolia71]

Good evening everyone,

On this forum I've been given (3/29/2005, Bernie Deitrick) formulas to
calculate time differences in column C base on the word "start" in column A
and Date +time in column B. "Start" appears from time to time. Now, I need
this time difference not only when the word "start" appears but each time the
word changes in column A. Words in column A are: start, fill, react, and
others. "fill" for example may appear several times before the word react
appears. I need the time spent between start and fill, between fill and react
and so on. All my trials failed to adapt the formulas.
Else, is there a way to adapt following formula for DIFFERENCE instead of SUM?
in column B =IF(A1="";"";SUM.IF($A$1:$A1;A1;$B$1:B1)). numbers in B have the
form [HH]:MM.
And a other question:
each time that in a row the word "Start" appears in column A with date+time
in column B,and batch number in column C, I want this date+time and batch
number be sent to a row located on a other worksheet but same file. The next
row with "Start" etc.. may be 10 rows underneath the previous. How can I do
it?
Thank you for any suggestion

68magnolia71

 

[Bernie Deitrick]

68magnolia71,

In C1, something like:

=IF(A1="Start",INDEX(B2:B1000,MATCH("Start",A2:A1000,FALSE))-B1,IF(A1="Fill"
,INDEX(B2:B1000,MATCH("React",A2:A1000,FALSE))-B1,""))

If you post a sample data set, tih your desired results, we'll be able to do
better.

HTH,
Bernie
MS Excel MVP

 

[68magnolia71]

Hello Bernie,

Thank you for your answer.

Column C Column D ColumnE Column F

Start 1/4/05 15:40 1/4/05 15:40 20:20
Wash 2/4/05 12:00 2/4/05 12:00 04:00
Treatment 2/4/05 16:00 2/4/05 16:00
Treatment 2/4/05 16:00 10:30
Emptying 3/4/05 2:30 3/4/05 2:30 10:00
Start 3/4/05 12:30 3/4/05 12:30 02:30
Fill catalyst 3/4/05 15:00 3/4/05 15:00 03:00
Acid Fill 3/4/05 18:00 3/4/05 18:00
Acid Fill 3/4/05 18:00
Acid Fill 3/4/05 18:00
Acid Fill 3/4/05 18:00
Acid Fill 3/4/05 18:00
Acid Fill 3/4/05 18:00
Acid Fill 3/4/05 18:00 32:00
Evaporation 5/4/05 2:00 5/4/05 2:00 14:00
Emptying 5/4/05 16:00 5/4/05 16:00 08:15

Date in french format ( US military format if you are in the US) Magnolia AK
for example.
Columns C&D data pouch in. In C is a drop down list with INDIRECT validation
depending on production type.
formula in E: =IF(D5="";E4;D5) row 4 through 97
formula in F: =IF(E6="";"";IF((E6-E5)=0;"";E6-E5)) row 4 through 97.
Attention: in french EXCEL "," is replaced by ";"
All this is rather clumsy, needs 3 columns where a powerfull formula would
use 1 column.
The worksheet is filled in once a 8 hours shift. If there is a phase change,
name of the phase and date/time is pouch in; if there no phase change the
phase name is pouched in but no date/time. This is the reason for the blanks
in D( this, because the phase and its starting date/time is beeing extracted
and sent at shift end, hence 3 times a day to a shiftreport file which in
turn is attached to a message and sent to relevant people.

I have actually thought of a IF pile in your formula. But is certainly
limited to 7.
Furthermore the phase names depend on production and therefore the number of
different phase name is pretty high and the their succession in production is
not always the same.

So this week-end I worked on your formula without success.
=SI(K4="Initiale";INDEX($L5:L$97;EQUIV("Initiale";$K5:K$97;FAUX))-L4;"")
=IF(K4="Initiale";INDEX($L5:L$97;MATCH("Initiale";$K5:K$97;FALSE))-L4;"")
It's not easy to convince EXCEL that I need "Initiale" replaced by any other
word from the list and MATCH ("initiale".... ) replaced by the next DIFFERENT
word down the column C. Then perform date/time "<>word w" minus date/time
"word w". The result is a phase time. Indeed from one word to the next
different word

Hopefully you will find a "elegant" solution. Also if I dare ask you to have
a look at the second question: how to teach a cell of a sheet to collect the
data in a cell of another sheet, IF in the same row the text "Initiale" has
been pouched in. The next row ---> data from next "Initiale"

Start (Initiale) 1/4/05 15:40
Start (Initiale) 3/4/05 12:30

I don't want all the stuff that is between the two "Initiale". Well,
copy/paste would do for me but this not my file. So it need be automatically
done.

Anyway thank you for the help. Don't spent too much time on it.

PS Certainly there is a better way to sent part of a EXCEL work sheet.
Unfortunately I don't know it.

68magnolia71,

In C1, something like:

=IF(A1="Start",INDEX(B2:B1000,MATCH("Start",A2:A1000,FALSE))-B1,IF(A1="Fill"
,INDEX(B2:B1000,MATCH("React",A2:A1000,FALSE))-B1,""))

If you post a sample data set, tih your desired results, we'll be able to do
better.

HTH,
Bernie
MS Excel MVP

Good evening everyone,

On this forum I've been given (3/29/2005, Bernie Deitrick) formulas to
calculate time differences in column C base on the word "start" in column A
and Date +time in column B. "Start" appears from time to time. Now, I need
this time difference not only when the word "start" appears but each time the
word changes in column A. Words in column A are: start, fill, react, and
others. "fill" for example may appear several times before the word react
appears. I need the time spent between start and fill, between fill and react
and so on. All my trials failed to adapt the formulas.
Else, is there a way to adapt following formula for DIFFERENCE instead of SUM?
in column B =IF(A1="";"";SUM.IF($A$1:$A1;A1;$B$1:B1)). numbers in B have the
form [HH]:MM.
And a other question:
each time that in a row the word "Start" appears in column A with date+time
in column B,and batch number in column C, I want this date+time and batch
number be sent to a row located on a other worksheet but same file. The next
row with "Start" etc.. may be 10 rows underneath the previous. How can I do
it?
Thank you for any suggestion

68magnolia71

 

[68magnolia71]

Hi Bernie,

It's magnolia again. I forget to mention it but I tried in you formula
=IF(K4="Initiale";INDEX($L5:L$97;MATCH("Initiale";$K5:K$97;FALSE))-L4;"")
to win a column and base the formula only on the presence or absence of a
date/time in column L. With the same amount of success than previously. There
are many empty cells between two cells containing a date/time. Tried to
replace "Initiale" with "ISNUM", NOTISEMPTY, etc...nothing doing.

68magnolia71

68magnolia71,

In C1, something like:

=IF(A1="Start",INDEX(B2:B1000,MATCH("Start",A2:A1000,FALSE))-B1,IF(A1="Fill"
,INDEX(B2:B1000,MATCH("React",A2:A1000,FALSE))-B1,""))

If you post a sample data set, tih your desired results, we'll be able to do
better.

HTH,
Bernie
MS Excel MVP

Good evening everyone,

On this forum I've been given (3/29/2005, Bernie Deitrick) formulas to
calculate time differences in column C base on the word "start" in column A
and Date +time in column B. "Start" appears from time to time. Now, I need
this time difference not only when the word "start" appears but each time the
word changes in column A. Words in column A are: start, fill, react, and
others. "fill" for example may appear several times before the word react
appears. I need the time spent between start and fill, between fill and react
and so on. All my trials failed to adapt the formulas.
Else, is there a way to adapt following formula for DIFFERENCE instead of SUM?
in column B =IF(A1="";"";SUM.IF($A$1:$A1;A1;$B$1:B1)). numbers in B have the
form [HH]:MM.
And a other question:
each time that in a row the word "Start" appears in column A with date+time
in column B,and batch number in column C, I want this date+time and batch
number be sent to a row located on a other worksheet but same file. The next
row with "Start" etc.. may be 10 rows underneath the previous. How can I do
it?
Thank you for any suggestion

68magnolia71