Bug 122158

Summary:	DAY(), MONTH() and YEAR() functions don't handle Gregorian cut-over
Product:	LibreOffice	Reporter:	Eike Rathke <erack>
Component:	Calc	Assignee:	Not Assigned <libreoffice-bugs>
Status:	NEW ---
Severity:	enhancement	CC:	mikekaganski, oliver.brinzing, philippe, xiscofauli
Priority:	medium
Version:	Inherited From OOo
Hardware:	All
OS:	All
Whiteboard:
Crash report or crash signature:		Regression By:
Bug Depends on:
Bug Blocks:	108827
Attachments:	10 days gap attachment 20181218

Description Eike Rathke 2018-12-17 16:56:42 UTC

In A1 enter date 1582-10-16
In A2 enter date 1582-10-15
Select A1:A2 and pull down a few cells, resulting dates are 1582-10-04, 1582-10-03, ..., correct due to the Gregorian calendar cut-over.
In B2 enter =DAY(A2) => 15
In B3 enter =DAY(A2) => 14  (wrong)
Similar for a date of 1582-09-30 the MONTH() function calculates 10 instead of 9.
For a date of 1581-12-31 the YEAR() function calculates 1582 instead of 1581.

The DAY(), MONTH() and YEAR() functions do not take the 10 days gap at the Gregorian calendar's cut-over date into account. The date serial numbers and number formatter display values are correct, hence subtracting 1582-10-04 from 1582-10-15 (i.e. =A2-A3) is indeed one day, and adding one day to 1582-10-04 (i.e. =A3+1) results in 1582-10-15.

Comment 1 Oliver Brinzing 2018-12-17 19:23:21 UTC

Created attachment 147634 [details]
10 days gap

Comment 2 Hiromi Kuramoto 2018-12-18 05:53:34 UTC

Created attachment 147637 [details]
attachment 20181218

I tried this reproduce in same way in macOS Sierra.
I can checked 10 days gap between 1582-10-15 to 1582-10-04, and =DAY, =MONTH, =YEAR  function also show same wrong.

Version: 6.1.3.2
Build ID: 86daf60bf00efa86ad547e59e09d6bb77c699acb
CPU threads: 4; OS: Mac OS X 10.12.5; UI render: default; 
Locale: en-US (ja_JP.UTF-8); Calc: group threaded

Comment 3 Eike Rathke 2018-12-19 23:47:24 UTC

So this is actually not a bug. ODFF even states that "evaluators should use a proleptic Gregorian system (continuing the years backwards as if the calendar existed in those years)", see http://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part2.html#__RefHeading__1018898_715980110

The problem is that the date serial number of a "date cell" usually is formatted using a locale specific calendar, or even any specified calendar available for a locale from the number formatter.

To solve this, we'd need

a) proleptic Gregorian (ISO 8601:2004) calendar date formats, e.g. with an [~ISO] calendar modifier, for which "ISO" is not yet specified in ODF 19.341 number:calendar but seems possible, http://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part1.html#__RefHeading__1417544_253892949

b) new spreadsheet functions that obtain the calendar used by the number format of the referenced cell given as argument, or take a parameter to specify the calendar; such as CALENDARDAY(), CALENDARMONTH() and CALENDARYEAR()

Comment 4 Regina Henschel 2023-10-15 14:49:47 UTC

*** Bug 144692 has been marked as a duplicate of this bug. ***

Comment 5 Regina Henschel 2023-10-15 14:52:00 UTC

*** Bug 149040 has been marked as a duplicate of this bug. ***