[Reproducible] with Parallel Dev-Installation of  "LibreOffice 3.5.0 Beta2- WIN7 Home Premium (64bit) German UI [Build-ID : 8589e48-760cc4d-f39cf3d-1b2857e-60db978]. References were MS OFFICE 2010, and MS EXCEL Viewer, als Gnumeric

May be there is some kind of EXCEL-bug what has to be mentioned for export of particular Numbers?
Comparison counting down shows

No      LibO Date     EXCEL date
61      01.03.1900    01.03.1900 
62      28.03.1900    29.02.1900   !!!!

Excel Counts 1900 as a leap year, but it was not
 <http://en.wikipedia.org/wiki/Leap_year#Gregorian_calendar>
 They also have a Saturday, January 0th  1900 in Redmont ;-)

Because of this we can't simply leave the "number behind the date" when we export to EXCEL.XLS (and import), but we have to modify numbers in Date formatted fields for import/export within particular date ranges.

That also never worked in OOo, so inherited.
Gnumeric seems to know the EXCEL bug and does it correct.

So this one is more or less a DUP of "Bug 37501 - FILEOPEN Calc shows wrong date in Excel created .xls".  But I disagree with decision "NOTOURBUG there. We claim to be able to open and edit EXCEL documents, and so we have to mention EXCEL bugs for the sake of compatibility.

I believe the bug is part of the "EXCEL-XLS spreadsheet data format, and a program that wants to be suitable for editing documents in that proprietary format have to compensate bugs in the data format definition (what here is done by the software EXCEL).

An extra evaluation has to be done for Office Open XML. I saved a test file as .xslx, opened it with Office 2010 and saw that for a "60" they still count the wrong 1900-Feb-29. 

The question is - Software bug or Data definition inconsistence? Concerning this more current (more or less) open format we have to be proactive.

ISO/IEC 29500-1:2008(E) has a definition in 18.2.28 workbookPr (Workbook Properties), pages 1742, 1743


date1904 (Date 1904):

Value that indicates whether to use a 1900 or 1904 date base when converting serial values in the workbook to dates. [Note: If the dateCompatibility attribute is 0 or false, this attribute is ignored. end note]

A value of 1 or true indicates the workbook uses the 1904 backward compatibility date system.

A value of 0 or false indicates the workbook uses a date system based in 1900, as specified by the value of the dateCompatibility attribute.

(See §18.17.4.1 for the definition of the date bases.)

The default value for this attribute is false.


dateCompatibility (Date Compatibility):

Specifies whether the date base should be treated as a compatibility date base or should support the full ISO 8601 date range.

A value of 1 or true indicates that the date system in use is either the 1900 backward compatibility date base or the 1904 backward compatibility date base, as specified by the value of the date1904 attribute.

A value of 0 or false indicates that the date system is the 1900 date base, based on the ISO 8601 date range.

(See §18.17.4.1 for the definition of the date bases.)

The default value for this attribute is true.



What's not obvious are the implications:

If dateCompatibility=false, the date base is 1900 and ISO 8601 Gregorian calendar used correctly without 1900-02-29.

If dateCompatibility=true and date1904=false, the date base is 1900 and Excel calculations include the broken behavior that pretends there would be 1900-02-29. Excel does not calculate dates before 1900-01-01.

If dateCompatibility=true and date1904=true, the date base is 1904. Presumably Excel does not calculate dates before 1904-01-01 so the 1900-02-29 problem would be moot (could someone verify?)


LibreOffice Calc implements the dateCompatibility=false model, and additionally knows a date1904=true mode with date base 1904 but still without 1900-02-29. We will not implement the broken dateCompatibility=true and date1904=false model to simulate wrong calendaring. As Calc for the date base 1900 uses the zero date 1899-12-30 instead of 1899-12-31, serial date numbers imported/exported from/to Excel produce correct dates for dates >=1900-03-01, but one day off for dates between 1900-01-01 and 1900-02-28.

What Calc should do when writing ISO 25000 file format is:

For 1900 date base write dateCompatibility=false. Apparently this is not done but dateCompatibility=true and date1904=false is written. To be verified.

For 1904 date base write dateCompatibility=true and date1904=true. Probably done already.


When reading ISO 25000 files:

For dateCompatibility=false use the Calc 1900 date base.
For dateCompatibility=true date1904=false use the Calc 1900 date base.
For dateCompatibility=true date1904=true use the Calc 1904 date base.
Probably all done already, but there needs to be a warning in the documentation about dates between 1900-01-01 and 1900-02-28.


Problem remains in roundtrip reading/writing; we could preserve the OOXML flag for 1900 dateCompatibility, but results would differ for the 59 days in question between Calc and further Excel operations. There's also no way to preserve that flag when saving to ODF, so latest then calculations would differ anyway. If we didn't preserve the flag, we'd differ once and future calculations would be identical. Saving to ODF would also be automatically correct. So I propose to not save the compatibility flag at all.

(In reply to Matej HALAC from comment #0)
> Steps to reproduce:
> 1. Type the date 11.1.1900 in a field.
> 2. Save as XLS.
> 3. Open in MS Office.
> 
> Current behavior:
> On LibreOffice the date is shown correctly. (11.1.1900) But when I open the
> XLS file in MS Office 2007 the date shows as 12.1.1900.

Reproduced.

Win 8 32-bit
MSO 2013
LibreOffice Version: 4.5.0.0.alpha0+
Build ID: 460b17d2712a80331a83329d2951f3e0303835cd
TinderBox: Win-x86@62-TDF, Branch:MASTER, Time: 2015-03-14_23:10:42
Locale: fi_FI

LO 5.1.2.2 Windows 7   Could not reproduce (with MS Excel 2010)

(In reply to sdc.blanco from comment #7)
> LO 5.1.2.2 Windows 7   Could not reproduce (with MS Excel 2010)

I still repro with MS Excel 2013
LibO Version: 5.2.0.0.alpha1+
Build ID: 16777b6bb0267c2b0602f1007a1e1fecac81329b
CPU Threads: 4; OS Version: Windows 6.2; UI Render: default; 
TinderBox: Win-x86@62-merge-TDF, Branch:MASTER, Time: 2016-04-29_05:45:00
Locale: fi-FI (fi_FI)

*** Bug 116572 has been marked as a duplicate of this bug. ***

Created attachment 175303 [details]
Excel 2016 showing dates before 1904 using base-1904

(In reply to Eike Rathke from comment #3)
> If dateCompatibility=true and date1904=true, the date base is 1904.
> Presumably Excel does not calculate dates before 1904-01-01 so the
> 1900-02-29 problem would be moot (could someone verify?)

What Excel does with pre-epoch dates in this mode is just hilarious - see the screenshot. Anyway, as you expected, the problem for pre-1904 would not occur.

Wrt dateCompatibility attribute of workbookPr:

there is also [MS-OI29500] "Office Implementation Information for ISO/IEC 29500 Standards Support" document, which is implementation notes for MS Office. It tells about the mentioned attribute [1]:

> g. The standard states that the dateCompatibility attribute determines whether
> the date base should be treated as a compatibility date base or should support
> the full date range of [ISO-8601].
> 
> Office ignores the dateCompatibility attribute, and always uses a compatibility
> date base.
> 
> This note applies to the following products: Office 2010, Office 2010 Server,
> Office 2010 SP1.
> ...
> j.   The standard states that the dateCompatibility attribute determines whether
> the date base should be treated as a compatibility date base or should support
> the full date range of [ISO-8601].
> 
> Office ignores the dateCompatibility attribute. If workbook@conformance equals
> "strict", the 1900 date system is used. Otherwise the 1900 compatibility date
> system is used. Excel does not support negative serial numbers.
> 
> This note applies to the following products: Office 2013 Client (Strict), Office
> 2013 Server (Strict), Office 2013 Client (Transitional), Office 2013 Server
> (Transitional).

And indeed, the later version of ISO/IEC 29500-1 (namely, 2016 [2]) does not mention "dateCompatibility" attribute at all.

Now the standard [2] defines workbook@conformance in "18.2.27 workbook (Workbook)":

> conformance (Document Conformance Class)
> Specifies the conformance class ... to which the SpreadsheetML document conforms.
> If this attribute is omitted, its default value is transitional.

MS implementer notes tell [3]

> b. The standard specifies that the conformance attribute is a valid attribute of
> the workbook element.
> 
> Excel ignores the conformance attribute.
> 
> This note applies to the following products: Office 2010, Office 2010 Server,
> Office 2010 SP1.

... which is expected, since MSO 2010 and earlier presumably did not know (or at least did not care at all) about that attribute.

So we *possibly* would want to specify the obsolete dateCompatibility attribute (for completeness?), but need to realize that it will never help.

Also we need to explore why we don't export conformance at all *when saving to "Office Open XML Spreadsheet"* (which is presumably our name for the "strict"?). In that mode, as far as I see, dateCompatibility *is* exported (with the expected zero significance).

[1] https://docs.microsoft.com/en-us/openspecs/office_standards/ms-oi29500/a251e7c6-7b8a-4e2f-b284-025f9f09ba3e
[2] https://www.iso.org/standard/71691.html
[3] https://docs.microsoft.com/en-us/openspecs/office_standards/ms-oi29500/7821b8f6-672a-4f05-bc0f-33b818210695

Dear Matej HALAC,

To make sure we're focusing on the bugs that affect our users today, LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed bugs which have not been touched for over a year.

There have been thousands of bug fixes and commits since anyone checked on this bug report. During that time, it's possible that the bug has been fixed, or the details of the problem have changed. We'd really appreciate your help in getting confirmation that the bug is still present.

If you have time, please do the following:

Test to see if the bug is still present with the latest version of LibreOffice from https://www.libreoffice.org/download/

If the bug is present, please leave a comment that includes the information from Help - About LibreOffice.
 
If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a comment that includes the information from Help - About LibreOffice.

Please DO NOT

Update the version field
Reply via email (please reply directly on the bug tracker)
Set the bug's Status field to RESOLVED - FIXED (this status has a particular meaning that is not 
appropriate in this case)


If you want to do more to help you can test to see if your issue is a REGRESSION. To do so:
1. Download and install oldest version of LibreOffice (usually 3.3 unless your bug pertains to a feature added after 3.3) from https://downloadarchive.documentfoundation.org/libreoffice/old/

2. Test your bug
3. Leave a comment with your results.
4a. If the bug was present with 3.3 - set version to 'inherited from OOo';
4b. If the bug was not present in 3.3 - add 'regression' to keyword


Feel free to come ask questions or to say hello in our QA chat: https://web.libera.chat/?settings=#libreoffice-qa

Thank you for helping us make LibreOffice even better for everyone!

Warm Regards,
QA Team

MassPing-UntouchedBug