Difference in time
 

Hi, i have a spreadsheet that pulls data from a trending program, it gives me
dates, times and steps. I would like to know if anyone can help me, in col A
are the dates, col B times and col C steps, can anyone tell me how i can get
excel to work out how long each step takes and put the answer in col D beside
the last time for that step, bearing in mind each step may differ in time
each time that step runs and there could be up to 5 steps each repetition and
the data could have up to 120 repetitions of each step. The data below is a
quick example.
Many thanks in advance for your help, Lewy.

08/04/2010 07:52:10 1
08/04/2010 07:52:15 1
08/04/2010 07:52:20 1
08/04/2010 07:52:25 1
08/04/2010 07:52:30 1
08/04/2010 07:52:35 1
08/04/2010 07:52:40 1 ?
08/04/2010 07:52:45 10
08/04/2010 07:52:50 10
08/04/2010 07:52:55 10
08/04/2010 07:53:05 10
08/04/2010 07:53:10 10
08/04/2010 07:53:15 10
08/04/2010 07:53:20 10
08/04/2010 07:53:25 10
08/04/2010 07:53:30 10
08/04/2010 07:53:35 10 ?

Difference in time
 

Try this

=IF(C1=C2,"",B1-B$1)

--

HTH

Bob

"Richard" wrote in message
...
Hi, i have a spreadsheet that pulls data from a trending program, it gives
me
dates, times and steps. I would like to know if anyone can help me, in col
A
are the dates, col B times and col C steps, can anyone tell me how i can
get
excel to work out how long each step takes and put the answer in col D
beside
the last time for that step, bearing in mind each step may differ in time
each time that step runs and there could be up to 5 steps each repetition
and
the data could have up to 120 repetitions of each step. The data below is
a
quick example.
Many thanks in advance for your help, Lewy.

08/04/2010 07:52:10 1
08/04/2010 07:52:15 1
08/04/2010 07:52:20 1
08/04/2010 07:52:25 1
08/04/2010 07:52:30 1
08/04/2010 07:52:35 1
08/04/2010 07:52:40 1 ?
08/04/2010 07:52:45 10
08/04/2010 07:52:50 10
08/04/2010 07:52:55 10
08/04/2010 07:53:05 10
08/04/2010 07:53:10 10
08/04/2010 07:53:15 10
08/04/2010 07:53:20 10
08/04/2010 07:53:25 10
08/04/2010 07:53:30 10
08/04/2010 07:53:35 10 ?

Difference in time
 

Copy and paste the below formula in D1 cell and drag it to the remaining
cells of D Column based on the D Column Data.

=IF(ISNA(IF(COUNTIF($C$1:$C1,$C1)=COUNTIF($C:$C,$C 1),INDEX(B:B,MATCH(C1,C:C,TRUE))-INDEX(B:B,MATCH(C1,C:C,FALSE)),"")),"",IF(COUNTIF( $C$1:$C1,$C1)=COUNTIF($C:$C,$C1),INDEX(B:B,MATCH(C 1,C:C,TRUE))-INDEX(B:B,MATCH(C1,C:C,FALSE)),""))


--
Remember to Click Yes, if this post helps!

--------------------
(Ms-Exl-Learner)
--------------------


"Richard" wrote:

Hi, i have a spreadsheet that pulls data from a trending program, it gives me
dates, times and steps. I would like to know if anyone can help me, in col A
are the dates, col B times and col C steps, can anyone tell me how i can get
excel to work out how long each step takes and put the answer in col D beside
the last time for that step, bearing in mind each step may differ in time
each time that step runs and there could be up to 5 steps each repetition and
the data could have up to 120 repetitions of each step. The data below is a
quick example.
Many thanks in advance for your help, Lewy.

08/04/2010 07:52:10 1
08/04/2010 07:52:15 1
08/04/2010 07:52:20 1
08/04/2010 07:52:25 1
08/04/2010 07:52:30 1
08/04/2010 07:52:35 1
08/04/2010 07:52:40 1 ?
08/04/2010 07:52:45 10
08/04/2010 07:52:50 10
08/04/2010 07:52:55 10
08/04/2010 07:53:05 10
08/04/2010 07:53:10 10
08/04/2010 07:53:15 10
08/04/2010 07:53:20 10
08/04/2010 07:53:25 10
08/04/2010 07:53:30 10
08/04/2010 07:53:35 10 ?

Difference in time
 

Messed that up, it should be

D2: =IF(C2=C3,"",B2-B$1-SUM(D$1:D1))

and copy down

--

HTH

Bob

"Bob Phillips" wrote in message
...
Try this

=IF(C1=C2,"",B1-B$1)

--

HTH

Bob

"Richard" wrote in message
...
Hi, i have a spreadsheet that pulls data from a trending program, it
gives me
dates, times and steps. I would like to know if anyone can help me, in
col A
are the dates, col B times and col C steps, can anyone tell me how i can
get
excel to work out how long each step takes and put the answer in col D
beside
the last time for that step, bearing in mind each step may differ in time
each time that step runs and there could be up to 5 steps each repetition
and
the data could have up to 120 repetitions of each step. The data below is
a
quick example.
Many thanks in advance for your help, Lewy.

08/04/2010 07:52:10 1
08/04/2010 07:52:15 1
08/04/2010 07:52:20 1
08/04/2010 07:52:25 1
08/04/2010 07:52:30 1
08/04/2010 07:52:35 1
08/04/2010 07:52:40 1 ?
08/04/2010 07:52:45 10
08/04/2010 07:52:50 10
08/04/2010 07:52:55 10
08/04/2010 07:53:05 10
08/04/2010 07:53:10 10
08/04/2010 07:53:15 10
08/04/2010 07:53:20 10
08/04/2010 07:53:25 10
08/04/2010 07:53:30 10
08/04/2010 07:53:35 10 ?

Difference in time
 

Richard,

Put this in d2 and drag down

=IF(C2=C3,"",(B2-B$1)-MAX($D$1:D1))
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Richard" wrote:

Hi, i have a spreadsheet that pulls data from a trending program, it gives me
dates, times and steps. I would like to know if anyone can help me, in col A
are the dates, col B times and col C steps, can anyone tell me how i can get
excel to work out how long each step takes and put the answer in col D beside
the last time for that step, bearing in mind each step may differ in time
each time that step runs and there could be up to 5 steps each repetition and
the data could have up to 120 repetitions of each step. The data below is a
quick example.
Many thanks in advance for your help, Lewy.

08/04/2010 07:52:10 1
08/04/2010 07:52:15 1
08/04/2010 07:52:20 1
08/04/2010 07:52:25 1
08/04/2010 07:52:30 1
08/04/2010 07:52:35 1
08/04/2010 07:52:40 1 ?
08/04/2010 07:52:45 10
08/04/2010 07:52:50 10
08/04/2010 07:52:55 10
08/04/2010 07:53:05 10
08/04/2010 07:53:10 10
08/04/2010 07:53:15 10
08/04/2010 07:53:20 10
08/04/2010 07:53:25 10
08/04/2010 07:53:30 10
08/04/2010 07:53:35 10 ?

Difference in time
 

Messed that up, it should be

So did I, of course it should be SUM and not MAX as in my effeort (:
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Bob Phillips" wrote:

Messed that up, it should be

D2: =IF(C2=C3,"",B2-B$1-SUM(D$1:D1))

and copy down

--

HTH

Bob

"Bob Phillips" wrote in message
...
Try this

=IF(C1=C2,"",B1-B$1)

--

HTH

Bob

"Richard" wrote in message
...
Hi, i have a spreadsheet that pulls data from a trending program, it
gives me
dates, times and steps. I would like to know if anyone can help me, in
col A
are the dates, col B times and col C steps, can anyone tell me how i can
get
excel to work out how long each step takes and put the answer in col D
beside
the last time for that step, bearing in mind each step may differ in time
each time that step runs and there could be up to 5 steps each repetition
and
the data could have up to 120 repetitions of each step. The data below is
a
quick example.
Many thanks in advance for your help, Lewy.

08/04/2010 07:52:10 1
08/04/2010 07:52:15 1
08/04/2010 07:52:20 1
08/04/2010 07:52:25 1
08/04/2010 07:52:30 1
08/04/2010 07:52:35 1
08/04/2010 07:52:40 1 ?
08/04/2010 07:52:45 10
08/04/2010 07:52:50 10
08/04/2010 07:52:55 10
08/04/2010 07:53:05 10
08/04/2010 07:53:10 10
08/04/2010 07:53:15 10
08/04/2010 07:53:20 10
08/04/2010 07:53:25 10
08/04/2010 07:53:30 10
08/04/2010 07:53:35 10 ?








.

Difference in time
 

Brilliant thank you, you have saved me hours of work.
--
Lewy


"Mike H" wrote:

Richard,

Put this in d2 and drag down

=IF(C2=C3,"",(B2-B$1)-MAX($D$1:D1))
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Richard" wrote:

Hi, i have a spreadsheet that pulls data from a trending program, it gives me
dates, times and steps. I would like to know if anyone can help me, in col A
are the dates, col B times and col C steps, can anyone tell me how i can get
excel to work out how long each step takes and put the answer in col D beside
the last time for that step, bearing in mind each step may differ in time
each time that step runs and there could be up to 5 steps each repetition and
the data could have up to 120 repetitions of each step. The data below is a
quick example.
Many thanks in advance for your help, Lewy.

08/04/2010 07:52:10 1
08/04/2010 07:52:15 1
08/04/2010 07:52:20 1
08/04/2010 07:52:25 1
08/04/2010 07:52:30 1
08/04/2010 07:52:35 1
08/04/2010 07:52:40 1 ?
08/04/2010 07:52:45 10
08/04/2010 07:52:50 10
08/04/2010 07:52:55 10
08/04/2010 07:53:05 10
08/04/2010 07:53:10 10
08/04/2010 07:53:15 10
08/04/2010 07:53:20 10
08/04/2010 07:53:25 10
08/04/2010 07:53:30 10
08/04/2010 07:53:35 10 ?

Difference in time
 

The OP seems to think yours is right!

--

HTH

Bob

"Mike H" wrote in message
...
Messed that up, it should be

So did I, of course it should be SUM and not MAX as in my effeort (:
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Bob Phillips" wrote:

Messed that up, it should be

D2: =IF(C2=C3,"",B2-B$1-SUM(D$1:D1))

and copy down

--

HTH

Bob

"Bob Phillips" wrote in message
...
Try this

=IF(C1=C2,"",B1-B$1)

--

HTH

Bob

"Richard" wrote in message
...
Hi, i have a spreadsheet that pulls data from a trending program, it
gives me
dates, times and steps. I would like to know if anyone can help me, in
col A
are the dates, col B times and col C steps, can anyone tell me how i
can
get
excel to work out how long each step takes and put the answer in col D
beside
the last time for that step, bearing in mind each step may differ in
time
each time that step runs and there could be up to 5 steps each
repetition
and
the data could have up to 120 repetitions of each step. The data below
is
a
quick example.
Many thanks in advance for your help, Lewy.

08/04/2010 07:52:10 1
08/04/2010 07:52:15 1
08/04/2010 07:52:20 1
08/04/2010 07:52:25 1
08/04/2010 07:52:30 1
08/04/2010 07:52:35 1
08/04/2010 07:52:40 1 ?
08/04/2010 07:52:45 10
08/04/2010 07:52:50 10
08/04/2010 07:52:55 10
08/04/2010 07:53:05 10
08/04/2010 07:53:10 10
08/04/2010 07:53:15 10
08/04/2010 07:53:20 10
08/04/2010 07:53:25 10
08/04/2010 07:53:30 10
08/04/2010 07:53:35 10 ?








.