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: NEEDINFO
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: 2025-03-04 11:33 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:

Attachments
Add an attachment (proposed patch, testcase, etc.)

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.
Comment 7 Xisco Faulí 2025-03-04 11:33:23 UTC 
Hello,
It's been a while since this issue was reported.
Could you please try to reproduce it with the latest version of LibreOffice from https://www.libreoffice.org/download/libreoffice-fresh/ ?
I have set the bug's status to 'NEEDINFO'. Please change it back to 'UNCONFIRMED' if the bug is still present in the latest version.