Issue 63813

Summary: (Auto-) Filter on dates for cells where Day-Month order is opposite to AOO Locale interchanges Day and month in filter selector
Product: Calc Reporter: robertpopa22 <robert.popa>
Component: formattingAssignee: AOO issues mailing list <issues>
Status: CONFIRMED --- QA Contact:
Severity: Normal    
Priority: P3 CC: issues, kpalagin, mroe.nospam, rainerbielefeld_ooo_qa
Version: 680m160Keywords: oooqa
Target Milestone: ---   
Hardware: All   
OS: All   
Issue Type: DEFECT Latest Confirmation in: 4.1.0
Developer Difficulty: ---
Description Flags
Autofilter issue for non En date formats
Sample Document
test spreadsheet with different locale dates
Sample with ISO format dates none

Description robertpopa22 2006-03-30 12:27:54 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.
Comment 1 Rainer Bielefeld 2006-03-30 13:06:15 UTC
@robertpopa22 ,
please attach a small sample file created with 680m160!

WFM with "2.0.2  German version WIN XP: [680m5(Build9011)]"
Comment 2 robertpopa22 2006-03-30 17:06:56 UTC
Created attachment 35395 [details]
Autofilter issue for non En date formats
Comment 3 robertpopa22 2006-03-30 17:11:27 UTC
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.
Comment 4 Rainer Bielefeld 2006-03-30 17:57:55 UTC
WFM with "2.0.2  German version WIN XP: [680m5(Build9011)]" for 'Autofilter non
EN issue.ods'

New problem with 680m160?
Comment 5 robertpopa22 2006-05-18 07:09:54 UTC
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. 

Comment 6 robertpopa22 2006-05-18 07:30:45 UTC
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
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
Comment 7 kpalagin 2007-04-14 09:12:20 UTC
Confirming with 2.2m8 on WinXP - set localle to Roman (Latin), open attached 
file, click autofilter arrow and click any date - nothing is selected.
Comment 8 frank 2007-04-16 14:28:06 UTC
Hi Eike,

have a look at this one please.

Comment 9 Rainer Bielefeld 2014-05-10 16:40:41 UTC
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
Comment 10 Rainer Bielefeld 2014-05-10 16:41:32 UTC
Created attachment 83370 [details]
Sample Document

Demonstrates result of research comment above
Comment 11 mroe 2014-05-12 12:48:18 UTC
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.
Comment 12 Rainer Bielefeld 2014-05-12 17:33:27 UTC
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
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?
Comment 13 Rainer Bielefeld 2014-05-12 17:34:24 UTC
Created attachment 83376 [details]
Sample with ISO format dates

See comment above how to use
Comment 14 mroe 2014-05-12 18:06:52 UTC
In your sample document Calc creates the following filter list:


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.
Comment 15 Rainer Bielefeld 2014-05-12 19:20:24 UTC
(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?
Comment 16 Rainer Bielefeld 2014-05-13 04:12:26 UTC
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.
Comment 17 Rainer Bielefeld 2014-05-13 04:16:00 UTC
Eike is not longer in AOO project
Comment 18 Marcus 2017-05-20 10:45:01 UTC
Reset the assignee to the default "".