dates 01.01.0001 - 03.01.0001 are after close and reopen changed to 30.12.1899 Steps to reproduce open new spreadsheet write date 01.01.0001 to cell save, reopen Date is now 30.12.1899
raal: that's excel compatibility... -.- (really)
No. Excel can't handle any date before 1900-01-01, but we can. Just that we store dates in a broken way, not completely finally analyzed yet but apparently some not strictly proleptic Julian calendar instead of a proleptic Gregoraian calendar. Dates starting at 0001-01-04 are at least read back correctly.
Created attachment 125712 [details] test ODS with column A formated as date -- with early CE dates When describing date issues helpful to use the ISO 8601 sequence YYYY-MM-DD -- in other words, here the affected date ranges are from 0001-01-01 to 0001-03-01. However, can not confirm. When input in ISO 8601 sequence--the YYYY-MM-DD are cast as dates into the cells. Would also note that year 1 BCE (e.g. YYYY - 0000) and earlier are not supported, as the date format does not handle BCE dates at all. But at least that is compliant with basics of ISO 8601--is there and OASIS ODF position on BCE dates?
Take a look at what is stored in the document package's content.xml with office:date-value attributes for such early dates and you'll see that it isn't a proleptic Gregorian date. BCE dates are supported by the file format, i.e. xmlschema-2 permits a minus sign, see https://www.w3.org/TR/xmlschema-2/#signallowed
(In reply to Eike Rathke from comment #4) > Take a look at what is stored in the document package's content.xml with > office:date-value attributes for such early dates and you'll see that it > isn't a proleptic Gregorian date. So meaning the date has been adjusted internally by LO to the Julian date as in this Wikipedia chart? https://en.wikipedia.org/wiki/Proleptic_Gregorian_calendar > > BCE dates are supported by the file format, i.e. xmlschema-2 permits a minus > sign, see https://www.w3.org/TR/xmlschema-2/#signallowed OK, W3C says it is allowed, but how would one enter the BCE date? Seems at this time in LO using U+002D prepended to the YYYY-MM-DD is not picked up as an ISO 8601 allowed date. Is there a different toggle? Dates I enter that way are being cast to 1899 -- is that somehow left over from MS Excel's mishandling?
Not related to Excel. If you enter -0001-01-01 it results in a formula =-1-1-1 which is -3 which is the date serial number of 1899-12-27 (with the common null date 1899-12-30). Though dates BCE can't be entered they can be calculated, for example in A1 enter 0001-01-01 and in A2 =A1-1 formatted as YYYY-MM-DD G The date calculation somehow is messed up though, the result for this example is 0002-12-31 BC, which at least is "unexpected" ... even with year 0 not being allowed. As for what exactly is stored in the file (down to 0001-01-03 for which it starts to break) I didn't finally investigate yet.
So.. this sheds some light. When I looked at some samples earlier I got confused and took things vice versa. Displayed is a "real" Gregorian calendar, i.e. the dates 1582-10-05 to 1582-10-14 don't exist and the calendar jumps from 1582-10-04 to 1582-10-15, dates prior to the Gregorian cutoff date are displayed in the Julian calendar. This makes sense. Stored to file are all dates in the proleptic Gregorian calendar, which is correct. But, the class and data structure doing that doesn't handle year 0 or negative dates, so 0001-01-04 is correctly stored as 0001-01-02, but 0001-01-03 is stored as 0000-12-31 and both 0001-01-02 and 0001-01-01 are stored as 0000-00-01. This of course is wrong. I'll dig into that.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=6d4f2dcc7cbba771e9d9b00de50368db4a88ef1b Resolves: tdf#100452 class Date full (BCE,CE) proleptic Gregorian calendar It will be available in 5.3.0. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
I have a question. thanks to commit related to this issue, calc now handlles dates later than 9957-06-26. Does this make ScInterpreter::ScEasterSunday's condition "|| nYear > 9956" unnecessary?(and do I need to file a separate bug report?) https://github.com/LibreOffice/core/blob/c0d4aadf08c16f2d79508367c631366f7a856346/sc/source/core/tool/interpr2.cxx#L321
*** Bug 88787 has been marked as a duplicate of this bug. ***