Bug 86996 - FILEOPEN: 1-1-1900 date in .xlsx and .xlsm from Excel changes when opened in LO
Summary: FILEOPEN: 1-1-1900 date in .xlsx and .xlsm from Excel changes when opened in LO
Status: RESOLVED NOTOURBUG
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:
Keywords:
Depends on:
Blocks:
 
Reported: 2014-12-04 09:02 UTC by Luuk
Modified: 2014-12-06 18:43 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:
Regression By:


Attachments
wrong attachment,.... ;-( please ignore (7.59 KB, text/html)
2014-12-04 09:02 UTC, Luuk
Details
test xlsx (7.54 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2014-12-04 09:02 UTC, Luuk
Details
test xlsm (7.59 KB, application/vnd.ms-excel.sheet.macroEnabled.12)
2014-12-04 09:08 UTC, Luuk
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Luuk 2014-12-04 09:02:33 UTC
Created attachment 110442 [details]
wrong attachment,.... ;-(
please ignore

I created two test documents in Excel 2013 with only one cell filled with the date '1-1-1900'

When opening the xlsx version, the date is shown correct.

When opening the xlsm version, the date is show as 31-12-1899, which is wrong.
Comment 1 Luuk 2014-12-04 09:02:55 UTC
Created attachment 110443 [details]
test xlsx
Comment 2 Luuk 2014-12-04 09:08:21 UTC
Created attachment 110444 [details]
test xlsm
Comment 3 tommy27 2014-12-06 13:52:33 UTC
tested under Win81 x64 with LO 4.3.4.1 and 4.5.0.0 master

I see 31/12/1899 in both files using LO
I see 01/01/1900 in both files using MS Excel Viewer

which is your O/S?
Comment 4 Luuk 2014-12-06 14:48:24 UTC
Windows 7, LO 4.3.4.1

I dont know what happened 2 days ago, but now i'm seeing the same as tommy27 is seeing. In LO: 31-12-1899  en in Excel2013 01-01-1900

BTW, on Linux, LO version: 4.1.6.2 i'm also seeing  31-12-1899

When opening this on a Mac, LO version 4.3.4.1, i also see 31-12-1899
opening with 'Numbers' on that Mac, shows 01-01-1900
(Mac has version 10.10.1 Yosemite)
Comment 5 tommy27 2014-12-06 15:00:14 UTC
same issue with LibO 3.3.3, OOo 3.3.0 and AOO 4.1.0
all those versions show 31.12.1899

bug is inherited from OOo. 
edited summary notes.
Comment 6 GerardF 2014-12-06 16:43:54 UTC
(In reply to tommy27 from comment #5)
> same issue with LibO 3.3.3, OOo 3.3.0 and AOO 4.1.0
> all those versions show 31.12.1899
> 
> bug is inherited from OOo. 
> edited summary notes.

No. Bug is in Excel.
Pretty all spreadsheets apps knows that 1900-02-29 do not exist, except Excel.
So all dates between 1899-12-31 and 1900-03-01 are offset by 1.

Don't use Excel for dates < to March, 1st of 1900.
Comment 7 tommy27 2014-12-06 18:22:14 UTC
great!!! I suspected that and I CC'ed Italo Vignoli who told me something like that few days ago... you anticipated his confirmation so I set status to RESOLVED NOTOURBUG.
Comment 8 Luuk 2014-12-06 18:43:31 UTC
Thanks for the clarification!

The worst thing happened when i checked this.

Microsoft puts the blame for this mistake to someone else
(http://spreadsheetpage.com/index.php/oddity/the_intentional_date_bug/)

A primary goal of computer programming is to write bug-free code. But did you know that Excel programmers created an intentional bug?

It's true. According to Excel, the year 1900 is a leap year. So if you enter the following formula, Excel won't complain, even though 29 February, 1900 is not an actual date:

=DATE(1900,2,29)

The reason for this error is compatibility. In the early days of personal computing, Lotus 1-2-3 was the most popular software available. Lotus programmers made the leap year mistake, and Microsoft programmers reproduced it so they could use the same date serial number scheme as 1-2-3.

Therefore, the days of the week prior to 1 March, 1900 are incorrect (e.g., 28 February, 1900 is really a Wednesday, not a Tuesday as report by Excel). In actuality, this is not a big deal because Excel doesn't even support dates prior to 1 January, 1900 -- which itself is an oddity.

Microsoft claims, perhaps rightfully so, that fixing the bug would create many more additional problems.