Time: Convert 1 min to 15 min interval

My data was captured every minute for a while - too often. When this was noticed, we switched to every 15 min. Original data sheet is 10,000 data points, and I have a few dozen of them. Extract from one of many data capture sheets is attached here.

Col D shows the interval...hardcoded by me for illustration for this forum.

Desired:
- I only need data from every 15, 30, 45, 00 minutes past the hour.
- The sheet has both 1 minute and 15 minute interval data, so my attempts to only apply my formulas to the 1 minute data are messing me up
- I'd like to round the timestamp to the nearest 15 minutes, but only AFTER I discard (for the 1 min interval data) the other 14 excessive data points.

Ideally, I end up with a much shorter sheet than I have now.

I don't mind using filters, once I have something that discriminates the data as a "15 minute" data point, to show me just that 15 min interval data.

Book3.xlsx

I am not an expert here, by any means. Be gentle please!

Help with how to FIND the 1st one- the first 1 minute interval data point? This is where I was having trouble on my own. I didn't know how to subtract time. I was trying to look for the delta between the timestamps, and if it was less than 15 minutes (or something bigger than 1 min), then identify it.

If the data were ALL 1 minute intervals, this would be easier for me. It's tricky for me (not an expert) because it's mixed.

Is there a reason why your data goes backwards? Do you need to keep it that way?

Pete

No, I can flip the data around, and put oldest first.

It might be easier if the data is in chronological order, but if you keep it like it is then you could put this formula in F2 for example:

=IF(TEXT(A2+B2-1/24/60,"hh:mm")=TEXT(A3+B3,"hh:mm"),"1 min","15 min")

then copy it down. It agrees with your hard-coded increments.

Hope this helps.

Pete

@blackspiral (or anyone else) Could the FIND formula be used to pull out the first entry for each time period, i.e. 9:00, 9:15, 9:30, 9:45, etc.? If so, please elaborate.

My need is similar, but my data is every second and I only need every 15 minutes, so I'm looking for a way to quickly reduce my file size so that my formula doesn't take so long to calculate.

Please forgive me if I should have started a new thread, this is my first question.

Many thanks,
Janette in Indy