Help with classifying events using time stamps

**LovelyHead** · 12-31-2017, 01:16 PM

Hello everyone,
I am currently working on a template document which gets raw data as input (time and event type) and has to count those different events in time intervals of 10 minutes. the lenghth of my measurements will always be 24hrs, with different set points. I have succeeded in making this, but a funny thing happens in the 10 minutes between 23:50:00 and 00:00:00 - if I try to change the time manually to 00:00, the problem happens with the calculations of the 10 minutes before, moreover, my set point is not constant, so using constant time values (instead the +Time (0,10,0) function) is a bit problematic...
A sheet containing the data for reference is accessible through here (it seems the uploader isn't working for me...) - https://drive.google.com/file/d/1CzN...ew?usp=sharing
Hoping there's something to do!

Thanks to anyone attempting to help~

**AliGW** · 12-31-2017, 01:22 PM

Attach the file here, please.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

**JohnTopley** · 12-31-2017, 01:51 PM

Try

in M6

=MOD(M5+TIME(0,10,0),1)

copy down

in N5

=IF(M6<M5,COUNTIFS($I:$I,N$3,$H:$H, ">="&$M5,$H:$H, "<"&$M6+1),COUNTIFS($I:$I,N$3,$H:$H, ">="&$M5,$H:$H, "<"&$M6))

copy down to N146