Apache OpenOffice (AOO) Bugzilla – Issue 110360
Autofiler on date column displays wrong info with YYYY-MM-DD;@ format (imported from Excel)
Last modified: 2017-05-20 11:11:37 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.
Created attachment 68515 [details] Excel source file for import
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.
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.
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.
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..
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.
See similar issue 63033.
Reset assigne to the default "issues@openoffice.apache.org".