Filtering Data Every 10 or 30 minutes

[jilex21]

Background:
The data collected from our proprietary software can be converted into Excel format using the file conversion utility. The data collected is for every minute. The 3-4 day run can generate 50000 rows. The first column is Date/time in the format of mm/dd/yyyy hh:mm AM/PM.

My question: How do we filter the data so that it will only show every 10 or 30 minutes, instead of every minute?

 

[Andrew Fergus]

Hi and welcome to MrExcel!

I recommend you use a helper column and then filter on the results of the helper column. Assuming your date/time record starts at A2, and column B is available, then enter the time interval you wish to filter on in cell B1 (e.g 10 or 30 etc). Then enter the following formula in cell B2 and copy down:

=MOD(MINUTE(A2),$B$1)=0

This will provide a TRUE/FALSE value if the time conforms to the interval value contained in cell B2. Set the filter based on the TRUE values.

HTH, Andrew

 

[jilex21]

Hi Andrew,

Thanks for the immediate reply.I did try your solution and it work. I can see TRUE or FALSE values.
I got three more questions.
1) How do I set the filter based on the TRUE values?
2) How do I involved the other 4-5 data columns that corresspond to each row in the Date/time column?
3) Do we need to convert the Date/time column into time only and in minutes?

Thank you.

 

[Andrew Fergus]

Hi

To create a filter, select the headings in the first row (assuming they contain the headings and the data starts in the row immediately below that row) then select menu option Data > Filter > Autofilter. This will give you a little drop down arrow on the cells you selected. When you click the arrow you can apply the filter by then selecting the TRUE value - this will then hide any rows that do not contain the value TRUE in that column.

I'm not sure what you mean by involving the other 4-5 columns. When you select the TRUE value in the filter, then all rows without the value TRUE will be automatically hidden.

If you have a column showing TRUE/FALSE for the 10/20/20 minute intervals, then you shouldn't need to change any columns to a time format.

I hope I have understood you correctly - please let me know if I haven't

Andrew

 

[jilex21]

Hi Andrew,

Thanks for the help. I'm still testing your solution in different data sets. So far so good. If I encounter problems, I may use this thread again or create a new one.

 

[Drdave1958]

I'd like to pile on this thread if I may. I'm trying to do almost the same thing except my time data is collected every two seconds and I want to filter it to every 5 minutes. I'm playing around with this...

=MOD(SECOND(C32),$B$28)=0

Where my data starts in cell C32 and my trigger is in cell B28.

Data looks like this...
14:31:44
14:31:46
14:31:48
14:31:50
14:31:52
14:31:54
14:31:56
14:31:58
14:32:00


Thanks,
Dave

 

[Drdave1958]

BTW, when I use this...

=MOD(MINUTE(C32),$B$28)=0

it works with a 5 in B28, but returns a true in every row that has the 5 minute interval, as follows....

14:35:00
14:35:02
14:35:04
14:35:06
14:35:08
14:35:10
14:35:12
14:35:14 ...etc.

I need it to ignore the seconds completely.

Thanks,
Dave

 

[Andrew Fergus]

Hi Dave

Try using this:
=MOD(ROUND(A32*288,7),1)=0

the 288 is 86400/300 (being 86400 seconds per day at 300 second intervals), rounding to 7 decimal places is to fix any floating point errors, and we want only to show TRUE for values which are perfect multiples of 288 seconds.

HTH, Andrew

 

[Drdave1958]

Thanks for your reply Andrew. I wound up using this solution from Gerald Higgins.

=AND(MOD(MINUTE(C32),$B$28)=0,SECOND(C32)=0)

Thanks again.

 

[RobSRoss]

any chance you would know how to average the data. For instance, I am logging temperature data at 1 second intervals. I am looking to take the average over the previous 60 seconds so my data is in Minutes. Any suggestions?