Thank you in advance for the help:

Here is the scene, a spread sheet, contains 5 columns, many rows as follows:
Col 1 - Date
Col - 2- Clock time i.e....02:25:45
Col -3 - GPM
Col - 4 - W/L
Col - 5 Data

What I need to do:


I would like to be able to filter the data by the time row, where I can eliminate all rows that do not equal my criteria.

I used to have a formula that was given to me years ago (which I have lost) that went something like this:

select all the data >data>filter>text filter>does not equal......... this is where I lost it. I need to be able to filter the "time" column by a selected minute , say I only want to filter the data by 5 minutes, or 10 minutes.

Thanks again everyone for the help

Why are Date and Time separate? Is the data imported from a logger or something? If you combine them, you should be able to filter right down to the date and minute with the canned filters. For whatever reason, filtering by time alone does not give this capability.

See the attached. Select the filter above Date/Time and you will see that I have filtered for Today from 1:27 pm through 1:29 pm.

Attached Files

TimeFilter.xlsx (49.6 KB, 13 views)

Yes the time is a critical component and is used as a basis for further analysis. the data is collected from a data logger. I had a formula (that I lost hence the request) that I could select the data set then apply a filter as I recall on a helper column to filter out "not equal to" but I don't remember how to write the helper form to separate the minutes

thank you for your response

Still seems to me there is no benefit to separating them other than that's how they come from the logger, but that's just me.

So long as there is only one date in the data, that can be done, but you risk grabbing data from other dates, which may be wholly unrelated, if there is more than one date.

At any rate, forget about the combined Date/Time then and just apply a filter and use the Between filter under Number Filters. If you just want from 1:27 to 1:29, enter those numbers and you're good to go.

I get your point I don't think I did a good job of describing what I need to do

I need to be able to evaluate the data set which is recorded every minute for 72 hours.

I want to see only the data that represents say every five minutes or every 15 minute etc....

the data criteria may change depending on different regulatory requirements

I'm not clear what you mean by "represents". The logger records every minute, but you only want the data from every fifth or fifteenth minute? Or do you want a running average of those measurements based on those intervals?

The former probably requires a helper column which should be fairly easy. 15 minutes in Excel is equal to 15/(24*60). You could set up a helper to only identify those rows where the mod() of the time with that ratio is zero or something like that.

For the latter, you can group and average with a pivot, but only by minutes, hours, days, etc. To do so by 5 minutes or 15 minutes would require a helper column which should be even easier.

Making it robust to manage the intervals to satisfy your regulations is mere window dressing. Should be pretty easy.

Thank you for the assistance

here is what I have come up with ...what do you think

1. Copy data to a new sheet and remove unneeded columns
2. Create a helper column and insert this code in the first cell of the helper column.
3. =(Mod(Minute(cell#), xx)) where "xx" = the number of minutes to filter by eg.. 5 or 10 or ….
4. Copy the formula down to the last row
5. Select all of the data rows
6. Select filter from the data menu
7. From the drop down arrows on the helper column select number filters > does not equal
8. In the box next to the "does not equal" enter “0” and select OK
9. Right click in the middle of the data and select delete rows.
10. You will now be left with only the rows containing the minutes selected.

I don't know. What do you think? Does it work?

yes it works as I remember it. I appreciate your attempts to understand my though process it helped to write it out
thanks again.