Bug 151183

Summary:	DATE spreadsheet function gives unexpected results for small year values, different from Excel
Product:	LibreOffice	Reporter:	Mike Kaganski <mikekaganski>
Component:	Calc	Assignee:	Not Assigned <libreoffice-bugs>
Status:	UNCONFIRMED ---
Severity:	normal	CC:	erack, miguelangelrv
Priority:	medium
Version:	unspecified
Hardware:	All
OS:	All
Whiteboard:
Crash report or crash signature:		Regression By:

Description Mike Kaganski 2022-09-26 07:06:08 UTC

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

Comment 1 Mike Kaganski 2022-09-26 07:53:49 UTC

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.

Comment 2 Mike Kaganski 2022-09-26 07:55:51 UTC

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.

Comment 3 m_a_riosv 2022-09-26 11:51:35 UTC

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

Comment 4 Mike Kaganski 2022-09-26 11:55:21 UTC

(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.

Comment 5 Eike Rathke 2022-09-26 15:52:20 UTC

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.

Comment 6 Eike Rathke 2022-09-26 15:57:30 UTC

(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.