how to calculate timestamp of 2 events which are irregularly spaced apart

Question

So I have an event log in which I would like to know the time elapsed between when a is true, and when b is false. then the timestamp difference will be calculated. I would like to know how to search the nearest previous a is true and refer to that timestamp to subtract with the b is a false timestamp. There may be an irregular number of event items in between.

as an extra requirement, I need to verify that the events are in the order: a=true -> b=true -> a=false -> b=false

I have tried to use if statement to confirm where i have a b is false, but don't know how to search up and refer to the last previous a is true.

also, I have no clue how to verify the sequence of 4 events in a certain order.

Answer 1

You could create a supportive column to help the formula identify.

=C2&D2

And then the main formula would be:

=if((C2&D2)="dFALSE",(B2-ARRAYFORMULA(MAX(IF($A$2:A2="aTRUE",$B$2:B2))))*1440,"")

But please be notified that this method can only be used when your data is time-sequenced.

Here is the example file I created in Google Sheet.

https://docs.google.com/spreadsheets/d/138tULefCoMdMHsOYIudugOssworU3wtzh1f7lEWelWs/edit#gid=0

If you need to use it on Excel, you may need to replace the arrayformula().