Bug 151183 - DATE spreadsheet function gives unexpected results for small year values, different from Excel
Summary: DATE spreadsheet function gives unexpected results for small year values, dif...
Status: UNCONFIRMED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
unspecified
Hardware: All All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks:
 
Reported: 2022-09-26 07:06 UTC by Mike Kaganski
Modified: 2022-09-26 15:57 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
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.