Bug 122158 - DAY(), MONTH() and YEAR() functions don't handle Gregorian cut-over
Summary: DAY(), MONTH() and YEAR() functions don't handle Gregorian cut-over
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected)
Inherited From OOo
Hardware: All All
: medium enhancement
Assignee: Not Assigned
: 144692 149040 (view as bug list)
Depends on:
Blocks: Calc-Function
  Show dependency treegraph
Reported: 2018-12-17 16:56 UTC by Eike Rathke
Modified: 2023-10-15 14:52 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:

10 days gap (11.02 KB, application/vnd.oasis.opendocument.spreadsheet)
2018-12-17 19:23 UTC, Oliver Brinzing
attachment 20181218 (12.84 KB, application/vnd.oasis.opendocument.spreadsheet)
2018-12-18 05:53 UTC, Hiromi Kuramoto

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

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