Issue 110360 - Autofiler on date column displays wrong info with YYYY-MM-DD;@ format (imported from Excel)
Summary: Autofiler on date column displays wrong info with YYYY-MM-DD;@ format (import...
Alias: None
Product: Calc
Classification: Application
Component: formatting (show other issues)
Version: OOO320m12
Hardware: All All
: P3 Trivial (vote)
Target Milestone: ---
Assignee: AOO issues mailing list
QA Contact:
Keywords: ms_interoperability
Depends on:
Reported: 2010-03-24 13:22 UTC by jbradshaw
Modified: 2017-05-20 11:11 UTC (History)
3 users (show)

See Also:
Issue Type: DEFECT
Latest Confirmation in: ---
Developer Difficulty: ---

Excel source file for import (18.00 KB, application/
2010-03-24 13:23 UTC, jbradshaw
no flags Details

Note You need to log in before you can comment on or make changes to this issue.
Description jbradshaw 2010-03-24 13:22:28 UTC
Actual: Dates show as Excel internal (numeric) format in an autofilter dropdown.
Expected: Autofilter dropdown would show values in same format as the cells in
the column (e.g YYYY-MM-DD).

As Excel uses an internal format (days since epoch) for dates, formatting needs
to be applied in the OOCalc autofilter otherwise the literal date is shown.

To recreate...
1. Create a new Excel spreadsheet that includes some dates in a column and add
an autofilter.  Ensure dates are changed to a date format. Save
2. Import into OOCalc. Dates in cells should display correctly.
3. Click on the autofilter and check the formats - should see formatted dates. 
I see a number showing days since epoch.
Comment 1 jbradshaw 2010-03-24 13:23:17 UTC
Created attachment 68515 [details]
Excel source file for import
Comment 2 ooo 2010-03-24 19:29:15 UTC
This is because the date format used
is not a "clean" date format, but contains the text format code as second
subformat. You can also observe that when opening the Format->Cells->Numbers
dialog on such a cell the format is listed under User-defined and not Date.
Actually with a blank sample entry because a negative number would be displayed
as empty.. If you change the format to
the filter values are displayed fine.

@dr: what Excel gimmick is that? Loaded in Excel the format is classified as
Date Afrikaans. Still, entering -1 in such a cell leads to the ###### because
they can't handle negative date serials.
Comment 3 daniel.rentz 2010-03-24 19:38:15 UTC
These cells point to XF 62, which refers to number format 165, which is a
user-defined number format containing the format string 'yyyy\-mm\-dd;@'.

So... Seems that Excel ignores the trailing parts of the format when it decides
whether it is date/time, exactly because they cannot cope with negative
date/time values.
Comment 4 daniel.rentz 2010-03-24 19:44:26 UTC
Hmm... This semms to be a speciality of @ indeed. I just tried the format
'YYYY-MM-DD;-0' in Excel which is not recognited as date format anymore, but as
custom. Entering negative values works as expected (value is shown), positive
values are shown as date.
Comment 5 ooo 2010-03-24 20:37:08 UTC
I just love Excel..

Apparently for filter entries even the actual subformat applied to the actual
value is considered by Excel, so categorizing such a format as date would only
solve this halfway in case someone used YYYY-MM-DD;-0 or a similar mix and
really had negative values. Still, ignoring subformats in categorization might
be a step into the right direction.

However, we should not have the "suppress negative values" behavior with code;@
that is wrong anyway and a trailing ;@ (respectively trailing section containing
@) always denotes the text subformat instead, regardless of its position. In
this case might be especially nasty because as opposed to Excel OOo does handle
negative date serials as values before 1899-12-30 and a user probably would not
like to have them displayed empty..
Comment 6 jbradshaw 2010-03-24 22:21:40 UTC
I'd call that ISO format (with UK locale), but yes Excel does also list it as a
custom format with the ;@.  When I clear the ;@ to make a new custom type and
then go back to ISO the ;@ reappears.  Bah.
Comment 7 niklas.nebel 2010-03-31 18:38:36 UTC
See similar issue 63033.
Comment 8 Marcus 2017-05-20 11:11:37 UTC
Reset assigne to the default "".