Time between events

Cross-posted at https://www.mrexcel.com/board/thread...vents.1220848/

Hello
I'm looking for a good idea as I am not sure where to start on this project.
I have a list of events, every time an alarm is raised, and every time the state goes back to normal. Sometimes the program makes two alarm or two normal events, but I am only looking at the first alarm and the first "back to normal" each time. (marked with green just to show)
I need to find the total time, the program has been in "alarm" and sum that up for the month (or between to dates)
I guess I have to start by making the timestamp column (A) a time, and not just text, but what then? How do I find an Alarm and look for the next Normal upwards in the sheet? I hoped I could look for the next "New state" in column G, but those seem random.
Thanks in advance for your input

Hi there,

I was not quite sure, but please have a look and see if the formulas in the attached work for what you need.

I have split your 'text' date into a date and a time column.
Then when we have an 'Alarm' we look for the next 'Normal' and take the time from that event.
From there we work out the difference.

If the times go from one day to the next, we will need to work on the date & time value to get the difference.

I trust it should give you a start and some ideas.

I played with a code option although it does change the sort order of your data, also converted your range to a table. See attached.

Administrative Note:

Is your forum profile showing the version of Excel that you need this to work for?

Members will tailor the solutions they offer to the version of Office (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this.

The three most recent versions of Excel are Excel 2019, Excel 2021 and MS365 - if you are using MS365, please give this name along with the release number in your profile (e.g. MS365 Version 2211). This is in the About Excel section further down the Account page.

Thanks.

Two other options with formulas:

Option 1 for all Excel 365 users:

Please try in O2:

Formula:

Please Login or Register  to view this content.

and in P2:

Formula:

Please Login or Register  to view this content.

and in Q2:

Formula:

Please Login or Register  to view this content.

Option 2 whole table incl header in 1 formula for users from Excel 365 with the newest functions available (from V2203)

Please try in S1:

Formula:

Please Login or Register  to view this content.

[Put the cursor in the formulabar before you paste formula option 2]

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not told us about this. You are required to do so.

Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important: https://excelguru.ca/a-message-to-forum-cross-posters/

I have added the link to your original post because you are a new member.