GNOME Bugzilla – Bug 164169
criteria parsing only checks against builtin formats
Last modified: 2010-04-30 17:41:42 UTC
Please describe the problem: Consider a spreadsheet with columns labelled 'date',.....etc. Dates are entered in non-US format d/mmm/yy (but other formats I have tried make no difference) Create filter criteria (say above and to the right of the data), eg to filter out lines whose dates lie outwith specified limits (specified in the same d/mmm/yy format). Often, other criteria on other columns are also included. The filter operation, if it doesn't crash, performs no filtering on dates. If a second numerical filter operation is included, that alone works. Steps to reproduce: 1.Make spreadsheet as above 2.Use data/filter/advanced filter/ to try and filter out a range of dates 3.Observe result Actual results: See above Expected results: I would expect the filter operation on dates, using for example < and > operators, to function in the same manner as for numbers Does this happen every time? yes Other information: The same operation has been found unreliable in Excel. It only worked for me when I split my spreadsheet into a number of smaller spreadsheets. The function 'date' is peculiar, in that its description (returning serial no) and its action (returning something else) seem incompatible. This may well have no relevance to the problem!!
Please give us a sample workbook I'm betting that what you think are dates are not being recognized, and are ending up as strings.
Created attachment 36211 [details] experimental spreadsheet for testing date filters Enclosed sample is just one version of the many variants I have tried
As expected the problem is with the date format. The criteria only check against the standard formats. Hence a criteria of >d-mmm-yy will not work. The simple solution for now is to use one of the builtin formats for the criteria. Our task will be to make this 'just work'. My best guess would be to add a fallback when parsing the criteria that would see if it can be parsed with the format associated with the first cell in the named field. It's a hack, but the alternatives of just parsing against all formats in the app/workbook/sheet seem prone to problems too. Anyone have any better ideas ?
This problem has been fixed in our software repository. The fix will go into the next software release. Thank you for your bug report. The problem of using only built-in formats was fixed long ago, but a typo prevented multiple criteria from working right. Now fixed.