Issue 110360

Summary:

Autofiler on date column displays wrong info with YYYY-MM-DD;@ format (imported from Excel)

Product:

Calc

Reporter:

jbradshaw <john>

Component:

formatting

Assignee:

AOO issues mailing list <issues>

Status:

CONFIRMED ---

QA Contact:

Severity:

Trivial

Priority:

CC:

daniel.rentz, issues, ooo

Version:

OOO320m12

Keywords:

ms_interoperability

Target Milestone:

---

Hardware:

All

OS:

All

Issue Type:

DEFECT

Latest Confirmation in:

---

Developer Difficulty:

---

Attachments:

Description	Flags
Excel source file for import	none

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
YYYY-MM-DD;@
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
YYYY-MM-DD
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 "issues@openoffice.apache.org".