Difference in time

  • Thread starter Thread starter Richard
  • Start date Start date
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 ?
 
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)),""))
 
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.
 
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.
 
Back
Top