Difference in time

R

Richard

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 ?
 
M

Ms-Exl-Learner

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,$C1),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(C1,C:C,TRUE))-INDEX(B:B,MATCH(C1,C:C,FALSE)),""))
 
B

Bob Phillips

Messed that up, it should be

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

and copy down
 
M

Mike H

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.
 
M

Mike H

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.
 

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

Top