Calculate time difference in repeating occurence

[Napoleoncarrot]

Hi I have a machine log of 10000 events. I previously got help on this forum to count the events, see below (apologies about dots working on mobile) :

Event . . . . Count

start . . . . . . . 0
Over pressure 1
over pressure 2
start . . . . . . . . 0
over pressure 1
start . . . . . . . . 0
over pressure 1
over pressure 2
Start . . . . . . . 0
start . . . . . . . 0
Start . . . . . . . 0
over pressure 1
start . . . . . . . . 0

These are all time and date stamped in additional columns, ideally I'd like to know the time difference from a "start" to an "over pressure" event. Each start (count =0) is the start of a new cycle

Thanks

 

[Oscar Cronquist]

[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td]start 0[/td][td]

0:00:00​

[/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]Over pressure 1[/td][td]

0:04:00​

[/td][td]

0:04:00​

[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]over pressure 2[/td][td]

0:05:05​

[/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td]start 0[/td][td]

0:13:05​

[/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td]over pressure 1[/td][td]

0:22:24​

[/td][td]

0:09:19​

[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td]start 0[/td][td]

0:25:36​

[/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7​

[/td][td]over pressure 1[/td][td]

0:32:15​

[/td][td]

0:06:39​

[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

8​

[/td][td]over pressure 2[/td][td]

0:35:31​

[/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

9​

[/td][td]Start 0[/td][td]

0:40:47​

[/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

10​

[/td][td]start 0[/td][td]

0:47:59​

[/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

11​

[/td][td]Start 0[/td][td]

0:51:00​

[/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

12​

[/td][td]over pressure 1[/td][td]

0:59:29​

[/td][td]

0:08:29​

[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

13​

[/td][td]start 0[/td][td]

1:04:41​

[/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

14​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet20[/td][/tr][/table]

Formula in cell C2:
=IF((LEFT(A1,5)="start")*(LEFT(A2,4)="Over"),B2-B1,"")

 

[Napoleoncarrot]

Thanks Oscar that's great, apologies for not adding in times. If I wanted to calculate the time difference from over pressure 2, should I repeated this formula into the false output and replace terms with overpressure 2 and B3? I simplified the above problem in real life I have overpressure 30, would excel handle this amount of calculations for 10,000 events?

 

[Oscar Cronquist]

Yes, it is a simple calculation. Excel should handle this.

Formula in cell C2:
=IF((LEFT(A1,5)="start")*(LEFT(A2,4)="Over"),B2-B1,IF((LEFT(A1,4)="Over")*(LEFT(A2,4)="Over"),B2+C1,""))