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.
Created attachment 147634 [details] 10 days gap
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
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()
*** Bug 144692 has been marked as a duplicate of this bug. ***
*** Bug 149040 has been marked as a duplicate of this bug. ***