| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 ? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 ? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 ? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 ? |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 ? |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 ? . |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 ? |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 ? . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Time formula (difference of predicted and actual time) | Excel Discussion (Misc queries) | |||
| find the difference between start time and end time when spanning. | Excel Discussion (Misc queries) | |||
| subtract the time difference from another time difference | Excel Discussion (Misc queries) | |||
| Time difference calculations, daylight savings time, Excel | Excel Discussion (Misc queries) | |||
| Negative time should be allowed in Excel, eg time difference | Excel Discussion (Misc queries) | |||
Linear Mode
