Filtering Data Every 10 or 30 minutes

jilex21

New Member
Joined
Jul 21, 2009
Messages
3
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?
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Andrew Fergus

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,432
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

New Member
Joined
Jul 21, 2009
Messages
3
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

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,432
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

New Member
Joined
Jul 21, 2009
Messages
3

ADVERTISEMENT

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

Board Regular
Joined
Mar 10, 2002
Messages
204
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

Board Regular
Joined
Mar 10, 2002
Messages
204

ADVERTISEMENT

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

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,432
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

Board Regular
Joined
Mar 10, 2002
Messages
204
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

New Member
Joined
Oct 25, 2013
Messages
1
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,129,468
Messages
5,636,492
Members
416,919
Latest member
twc2c

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top