Classifying time-stamped events into time intervals

Thank you for your input. This very well classifies to which interval each event belongs to. However, what I need is to count how many seconds of that event happen within that given interval. I've replied under for more details

Thank you very much for your input. I've been now working into this and have come around a better way of presenting my data.

To go straight to the point, you can see the list of time-stamped events on the "data" attached file (columns A to C)

The set of 80 second time intervals (columns D and E, F is just a descriptive column) are also there.

What I need to do is to create a table sumarizing the duration (in seconds) of all the existing events during each time interval in columns D and E.

Ideally, I'd like my output to look like the "output" attached file. In the empty cells should be the duration of each event during the indicated time interval, the total duration in seconds of all events should therefore be 80 as this is an 80 second interval.

My question is wether this can be done in Excel or I need a different software. Maybe R?

Hope I can get some more advice!

Thanks for your reply. In the original post "sample".xlsx the time assigned to each event is the start of the event.


Yes, in the file data.xlsx I've changed the layout since I though that would make it more clear.


Anyways, I've also been doing some work on the file you uploaded and have added a column with the end of each event (column C). Hope this helps.


As you can see I've been able to create a column counting the seconds of each event in the given interval (column I), although when there's a time mismatch between the interval in which the event happened and the set 80 second interval, I'm left with a surplus (column J), that I'm not able to match with the given interval.


Maybe you know how to do this? As an example cell J2 has 11 seconds of surplus. Ideally, there should be a new 3rd row with "Head up" as an Event (A3), interval 2 in cell E3 (because the interval 1 has already been "saturated"), and Event duration in interval I3 should be 11 seconds (the 11 seconds of surplus), and so on.


This needs to be done everytime the surplus value is not 0. For big surplus values (ex. J5), several rows should be created "filling" the 80 second time intervals.


Anyways, any input is appreciated as always! Thanks in advance for your time.

Hello! Don't know if my previous message was posted but yes, number 1) option seems perfectly fine to me.

Please let me know if you need any additional information. Thanks again for your interest.