Apache OpenOffice (AOO) Bugzilla – Issue 63813
(Auto-) Filter on dates for cells where Day-Month order is opposite to AOO Locale interchanges Day and month in filter selector
Last modified: 2017-05-20 10:45:01 UTC
Autofilter doesn't work on fields containing dates not in En format. For example the Romanian DD.MM.YYYY lets the autofilter to select the distinct values in range BUT when the filter is applied there is no match found.
@robertpopa22 , please attach a small sample file created with 680m160! WFM with "2.0.2 German version WIN XP: [680m5(Build9011)]"
Created attachment 35395 [details] Autofilter issue for non En date formats
Though the data selected by the Autofiter's combobox is really what you can find in the document, when it searches for matches it doesn't keep track of the format applied on the field. For example the 1st of January 2006 is 01/01/2006 in En format (mm/dd/yyyy) and 01.01.2006 in Ro format (dd/mm/yyyy). When autofilter is applied for this it finds a match (because the month and day corespond) But for the 2nd of January 2006 there will be no more matches. En 01/02/2006 and Ro 02.01.2006 Please check this out in the attached file.
WFM with "2.0.2 German version WIN XP: [680m5(Build9011)]" for 'Autofilter non EN issue.ods' New problem with 680m160?
Evrika. New information! The problem still exists... After further study I found that even with 2.0.169 the autofilter selects the distinct values from the column it filters BUT displays them in the dropdown combobox IN THE REGIONAL SHORT DATE FORMAT. When a filter is applied it compares the default regional short date format with the values in the column therefore it does not finds any match. BUT this problem appears if you use a different language format on the column than the REGIONAL SETTINGS chosen one, and only if the MONTH is switched with the DAY (example: USA format MM/DD/YYYY with Romanian DD.MM.YYYY) Please ask me if this is not clear enough. The problem is very old and I thik it is not so hard to resolve. Thanks!
I could duplicate the problem on a different PC only for this situation: The Regional Setting was Romanian (DD.MM.YYYY) 1. If The column with dates was formated D MMM YYYY, language Roamnian, it works well. 2. If The column with dates is any DD.MM.YYYY, it works well. 3. If the column with dates was even UK English !!! it worked 4. BUT when the column was USA (MM/DD/YYYY) it did not work For duplication use a simple column with different dates on which you apply a autofilter.
Confirming with 2.2m8 on WinXP - set localle to Roman (Latin), open attached file, click autofilter arrow and click any date - nothing is selected.
Hi Eike, have a look at this one please. Frank
I see the effect, opening document with German WIN AOO 4.1.0 everything works fine, but after I changed Menu 'Tools -> Options -> Language Settings -> Language -> Locale' to "Italian" autofilder finds nothing when I select one existing item in the Filter Selectof. But don't understand some effects, for me it' completely unclear what the bug might be. Some research ------------- (a) Reporter's sample document is a little strange. Why do the autofilter column heading cell in menu 'Format -> Cells -> Numbers -> Language' show "Default" language, but cells with date "Romanian"? In sheet "Working Autofilter" all cells are "Default". (b) Repair of (a) (all to "Romanian) does not heal the problem with 'Tools -> Options -> Language Settings -> Language -> Locale = "Italian"' (c) But 'Format -> Cells -> Numbers -> Language = Italian' for all cells heals the problem. (d) With a new document, same values as in sample and all cells "Standard" Language (=locale) I never ran into problems with own sample documents, but as soon as I apply cell-locale I run into problems (e) It seems some particular locale mismatch causes problems, but I still do not understand all details I will need some more research for deeper insight
Created attachment 83370 [details] Sample Document Demonstrates result of research comment above
Created attachment 83375 [details] test spreadsheet with different locale dates With the attached test spreadsheet you can try how the AutoFilter in Calc for dates works. A. Calc retrieves the values, the type of the value and the locale from the cells. B. With this information Calc creates the entries for the sorted filter list. C. If you pick an entry from the filter list and it is a date, Calc uses the locale setting of AOO to calculate the datevalue. => BUG: If the order of the day and month differs from the locale setting of AOO the wrong date is calculated: 01/05/2014 ↔ 05/01/2014 and vice versa. EXCEPTION: Is the day greater than 12 it will always been treated as day: 05/13/2014 stays 05/13/2014. Possible solution: a) The AutoFilter must remember the used locale for a date or b) it must remember the datevalue itself.
Currently I can't find any exception from rule "Autofilter on date fields does not find cells where {Day-Month order of cell Format Locale} is opposite to AOO Menu 'Tools -> Options -> Language Settings -> Languages -> Locale', what more or less is the same what what mroe found out. Not the real Day-Month-order in cell does matter, but the Day-Month-order of cell format Locale? I still do not understand Example: With AOO Locale EN-US (M-D-Y) 2014-01-08 (eighth of January) will be found if cell has German (D-M-Y) ??? 2014-01-09 (ninth of January) will NOT be found if cell has EN-GB (D-M-Y) 2014-01-10 (tenth of January) will be found if cell has EN-US (M-D-Y) 2014-01-11 (11th of January) will NOT be found if cell has Italian (D-M-Y) For me it's plausible that B3 will not be found (Ger locale) and that B12 will not found (Italian locale, same order as German). But why does filter find B9 with German locale? Might "somehow" have to do with order of application of AOO Locale and Cell locale?
Created attachment 83376 [details] Sample with ISO format dates See comment above how to use
In your sample document Calc creates the following filter list: 02.01.2014 01/03/2014 01/04/2014 01/05/2014 06/01/2014 01/07/2014 01/08/2014 09/01/2014 01/10/2014 11/01/2014 01/12/2014 01/13/2014 01/14/2014 01/15/2014 01/16/2014 sorted from 2. Jan. to 16. Jan. 01/08/2014 is right for M/D/Y so it will be calculated to the right date value and B9 will be found. 09/01/2014 is false for M/D/Y so it will be calculated to the right date value for 1. Sept which is not in the filter area. Same for 11/01/2014.
(In reply to mroe from comment #14) Yes, that's the visible effect. The thing I don't understand is the logic behind that date filter item calculation. I'm too tired today, will try tomorrow when well rested. Or may be that's simply nonsense, can't be understood?
Old problem, I already see it in OOo 1.1.5. I still do not understand Selector result for B3, but nevertheless, this is a bug.
Eike is not longer in AOO project
Reset the assignee to the default "issues@openoffice.apache.org".