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]

	A​	B​	C​	D​
1​	start 0	0:00:00​
2​	Over pressure 1	0:04:00​	0:04:00​
3​	over pressure 2	0:05:05​
4​	start 0	0:13:05​
5​	over pressure 1	0:22:24​	0:09:19​
6​	start 0	0:25:36​
7​	over pressure 1	0:32:15​	0:06:39​
8​	over pressure 2	0:35:31​
9​	Start 0	0:40:47​
10​	start 0	0:47:59​
11​	Start 0	0:51:00​
12​	over pressure 1	0:59:29​	0:08:29​
13​	start 0	1:04:41​
14​

Sheet: Sheet20

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,""))