Ask Your Question

selecting a date range for an advanced filter

asked 2019-02-09 15:04:14 +0100

Peter Keogh gravatar image

updated 2020-08-18 13:22:45 +0100

Alex Kemp gravatar image

I have a large number of rows in a calc sheet with one column headed "Date". How can I specify an advanced filter to select rows between specific dates? A filter with 2 columns headed "Date" holding criteria of the lower and upper limits does not work. A filter with 1 column headed "Date" with criteria of " AND " between the upper and lower limits does not work.

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted

answered 2019-02-09 18:27:02 +0100

Peter Keogh gravatar image

You can create a new column (header DateTest) in the target sheet which tests the Date against the two range limits giving a true or false value. The advanced filter can then check for DateTest=TRUE. This works but it is not elegant.

edit flag offensive delete link more

answered 2019-02-10 13:23:07 +0100

Hello @Peter Keogh

A filter with 2 columns headed "Date" holding criteria of the lower and upper limits does not work.

It does work and is the correct way to define multiple conditions for desired column to be filtered, cause columns in criteria table are logically connected with AND operator. So when creating the condition table for advanced filtering, you shall repeat column header multiple time and define each condition separately. Please see this sample file how filtering by date with lower and upper limits can be implemented. File contains macro to re-apply advanced filter without need to go the menu and select Filter source and Copy-to source each time condition changes.

If the above does not work for you, I suspect that your Date column contains text string representing the date, not the real Datetime numberic value, so the advanced filter can not correctly compare date value from the filter criteria against text string from the data source, so it returns wrong result. If it is so, you can select whole Date column, go to menu Data -> Text to Columns, right click on the column preview displayed at the bottom of dialog window and select date format needed. It shall convert text to Datetime value. Any conversions shall be implemented after backup copy of the original file is made! If there is some other thing preventing filter to work as expected, please share sample file for inspection.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2019-02-09 15:04:14 +0100

Seen: 1,059 times

Last updated: Feb 10 '19