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.
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.
Hello @PeterKeogh
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.