Sorting timed events into interval time data

**dixon** · 08-19-2010, 12:13 PM

This is my probablem:

I have two separate data sets; one is a column of temperature data collected every 10 minutes (interval data) and the other a column of event data (which can be thought of as a random time), which records time when a window is opened or closed.
I want to sort the event data into a new column so that the time of its occurance will fall between the appropriate 10 minute interval eg event time 08:41:07 to fall between interval time 08:40:00 and 08:50:00. I am not quite sure what to do with multiple events between one 10 minute interval!

Example data...

28/07/2010 08:00:00 08:41:07
28/07/2010 08:10:00 08:46:38
28/07/2010 08:20:00 09:02:29
28/07/2010 08:30:00 09:10:45
28/07/2010 08:40:00 09:12:01
28/07/2010 08:50:00 09:17:38
28/07/2010 09:00:00 09:18:13
28/07/2010 09:10:00 09:19:06
28/07/2010 09:20:00 09:22:18
28/07/2010 09:30:00 09:25:08
28/07/2010 09:40:00 09:25:49
28/07/2010 09:50:00 09:26:02
28/07/2010 10:00:00 09:39:44

**Cheeky Charlie** · 08-19-2010, 02:34 PM

Ithink we could do something with this, but:

I am not quite sure what to do with multiple events between one 10 minute interval!

You need to resolve this as I'm not keen to do it three times while you make your mind up...

**dixon** · 09-06-2010, 10:39 AM

Hi,

Thanks for the reply and fair comment. For now I will only consider events that last more than 10 minutes, so I will ignore multiple events that occur within 10 minute periods. Looking through the data, there doesn't appear to be to many of these occurrences anyway!
Many thanks

**Cheeky Charlie** · 09-06-2010, 11:06 AM

So if your first two columns are in A and B, and your event data is in G
C1=LOOKUP(B2,$G$1:$G$13)
D1=IF(ISERROR(C1),"",IF(C1<B1,"",C1))

copy both down

Obviously(?) this won't work if your data spans midnight - we can work around this if necessary.

NB it would be very easy to inset the formula for C1 into the formula in D1 (everywhere where C1 is written) - but this would make it much harder to do anything with later.

Let us know how it goes.