In Calc: =DATE(6;1;-2) gives 2005-12-29 In Excel the same function gives 1905-12-29. ODFF [1] does not specify that the function depends on HOST-NULL-YEAR. [1] https://docs.oasis-open.org/office/OpenDocument/v1.3/OpenDocument-v1.3-part4-formula.html#DATE
OTOH, what Excel gives is also confusing: why "year 0" must mean 1900? If at all, DATE(6;1;1) should give 0006-01-01, not something related to HOST-NULL-DATE. Maybe use of HOST-NULL-YEAR here is more substantiated. The question arose in https://forumooo.ru/index.php?topic=9386.msg63874, about the Excel formula that stopped working for the reporter: =A1+DATE(6;1;-2) where A1 had a date, to which the reporter wanted to add 6 years. The formula is conceptually wrong, though (adding two serial dates makes no sense), so WONTFIX/NOTABUG is a valid resolution.
Forgot to mention, that OP mentioned there, that the formula also worked "not ideally", requiring them to change "days" constantly ("-1" or "-2"), so not fixing a broken-by-design and non-working formula is valid.
Hi @Mike, the LibreOffice behaviour, looks depends on the option: Menu/Tools/Options/LibreOffice/General - Year (two digits) with it to 1900-1999, does equal then excel -> 1905-12-29
(In reply to m.a.riosv from comment #3) Yes, you are completely correct: the mentioned setting is what the standard calls HOST-NULL-YEAR.
There's not much we can do about this. If we changed the two-digits year input window (for this function only) to 1900-1999 for a hard-wired Excel compatibility then documents that rely on the user's HOST-NULL-YEAR setup would break.
(In reply to Mike Kaganski from comment #1) > OTOH, what Excel gives is also confusing: why "year 0" must mean 1900? If at > all, DATE(6;1;1) should give 0006-01-01 That's not confusing, Excel simply does not handle dates before its null-date 1900-01-01, at all.