Bug 44453 - FILESAVE FILEOPEN .xls: EXCEL Leap year bug has to be mentioned
Summary: FILESAVE FILEOPEN .xls: EXCEL Leap year bug has to be mentioned
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: Other All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard: BSA
Keywords:
: 116572 (view as bug list)
Depends on:
Blocks: Number-Format XLSX XLS
  Show dependency treegraph
 
Reported: 2012-01-04 07:08 UTC by Matej HALAC
Modified: 2018-10-07 06:59 UTC (History)
5 users (show)

See Also:
Crash report or crash signature:


Attachments
File showing the date 11.1.1900 and 11.1.2011. (6.50 KB, application/vnd.ms-excel)
2012-01-04 07:08 UTC, Matej HALAC
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Matej HALAC 2012-01-04 07:08:19 UTC
Created attachment 55111 [details]
File showing the date 11.1.1900 and 11.1.2011.

Problem description: 
Date will show different values in MS Office and Libre Office. This only occurs when the year is set to 1900. Works fine with 2011 as the year.

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.

Expected behavior:
Should look the same on both I think.

Platform (if different from the browser): 
              
Browser: Mozilla/5.0 (X11; Linux i686; rv:7.0.1) Gecko/20100101 Firefox/7.0.1
Comment 1 Rainer Bielefeld Retired 2012-01-04 11:02:25 UTC
[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.
Comment 2 Rainer Bielefeld Retired 2012-01-04 11:39:35 UTC
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.
Comment 3 Eike Rathke 2012-01-05 08:59:09 UTC
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.
Comment 4 QA Administrators 2015-02-19 15:36:56 UTC Comment hidden (obsolete)
Comment 5 Buovjaga 2015-03-16 18:52:52 UTC
(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
Comment 6 tommy27 2016-04-16 07:24:30 UTC Comment hidden (obsolete)
Comment 7 sdc.blanco 2016-04-29 23:13:13 UTC
LO 5.1.2.2 Windows 7   Could not reproduce (with MS Excel 2010)
Comment 8 Buovjaga 2016-05-13 14:05:03 UTC
(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)
Comment 9 QA Administrators 2017-05-22 13:38:58 UTC Comment hidden (obsolete)
Comment 10 Aron Budea 2018-03-24 23:37:54 UTC
*** Bug 116572 has been marked as a duplicate of this bug. ***