Created attachment 174887 [details] File with date set rows demonstrating the issue. The auto-extrapolation of months doesn't work if days are excluded. If you have a row of fields with dates that exclude: 2019-10 2019-11 2019-12 etc, and you set the date field to ISO 8601, when you ask Calc to guess the next field, it brings up: 2019-13 2019-14 etc It's subjective if that's a date given technically ISO 8601 requires the day as well. However, if I then do the same thing but specify the field type as user customised: YYYY-MM - you get the exact same behaviour: 2019-13 2019-14 etc Expected in both cases is: 2020-01 2020-02 etc
(In reply to buggymcbug from comment #0) > 2019-10 > etc, and you set the date field to ISO 8601 > It's subjective if that's a date given technically ISO 8601 requires the day > as well. No it is not "subjective", it is objectively *not* a date. What you enter is just a text with come numeric characters separated by a dash; this text can't be converted to a date, and as such it is kept as text in any cell, no matter how you *format* it. You may e.g. check that the cell in fact contains the text, using View->Value Highlighting (Ctrl+F8). For such a text, the only thing that Calc is able to do when drag-copying is to notice that the string ends with some contiguous run of numeric characters; and treat those characters the same way as it would e.g. for "value-01", "value-02", .... Closing NOTABUG. There's no "date without day" thing in Calc.
> No it is not "subjective", it is objectively *not* a date. Depends on your definition of "date". It doesn't *have* to be a day, another acceptable definition is: "a Point in time". A particular month is by that definition a date: https://en.wiktionary.org/wiki/date#Noun_2 (#3) Either way, even if you believe that ISO8601 isn't a valid date without the day (which is fine, it is a standard, hence subjective), a user-specified date that is of type YYYY-MM seems like a perfectly valid date to me. Calc knows this is a date because I've told it that it is. Given this it seems reasonable to assume it should be know that 2019-12 is followed by 2020-01. At least, I'd consider that a bug in my own programs.
(In reply to buggymcbug from comment #2) > > No it is not "subjective", it is objectively *not* a date. > > Depends on your definition of "date". LOL. It does not depend on "my" definition; it is what standard say, and what is implemented in software. It is the other way round: there is a definition that is used in the industry, and you are trying to tell that a random definition from outside is usable here :) And again: formatting has nothing to do with *what you enter* - it's only about *how to display* a valid entry. Since you do not enter valid dates, they can't be displayed using *any* date format - you only see text, that happens to match the format, but does not use the format.
But if you want to *enter* dates using such a notation, you need to tweak *date acceptance patterns* at Options->Language Settings->Languages [1]. There you may specify a Y-M pattern, and then when you enter 2019-01, Calc will convert it to 2019-01-01 (implicitly adding the minimal value of the missing element). Then the resulting *proper date* in the cell may be displayed according to the format that you choose. [1] https://help.libreoffice.org/7.2/en-US/text/shared/optionen/01140000.html?DbPAR=CALC#bm_id9471429
> LOL. It does not depend on "my" definition; it is what standard say, and what is implemented in software. I'm afraid the standard agrees with me. This part of the spec: https://datatracker.ietf.org/doc/html/rfc6350#section-4.3.1 "Reduced accuracy, as specified in [ISO.8601.2004], Sections 4.1.2.3 a) and b), but not c), is permitted." So the ISO8601 specification explicitly says that a date of 1985-04 is valid (it uses that as an example). (Wikipedia says the same thing (with the standard as a ref): https://en.wikipedia.org/wiki/ISO8601#Calendar_dates "The standard also allows for calendar dates to be written with reduced precision. For example, one may write "1981-04" to mean "1981 April". " So hope you don't mind but given the spec itself explicitly says this is a valid date, I'm going to re-open. > There you may specify a Y-M pattern, and then when you enter 2019-01, Calc will convert it to 2019-01-01 Thanks, but that's false precision (https://en.wikipedia.org/wiki/False_precision) and is exactly the behaviour I do not want (and indeed is probably why the spec does allow reduced precision).
(In reply to buggymcbug from comment #5) > > LOL. It does not depend on "my" definition; it is what standard say, and what is implemented in software. > > I'm afraid the standard agrees with me. This part of the spec: > https://datatracker.ietf.org/doc/html/rfc6350#section-4.3.1 The only standard that is discussed here is ODF; you constantly try to bring unrelated matters into discussion; you started mentioning ISO 8601 etc. I mentioned the *implemented* standard - and it is ODF 1.3 currently. The relevant part there is ODF 1.3 part 4 Formula sect. 4.3.3 Date [1]: > Date is a subtype of Number. > Date is represented by an integer value. > A serial date is the expression of a date as the number of days elapsed from > a start date called the epoch. This definition explicitly says that dates, as a data sub-type in spreadsheets implementing ODF, are measured in *days*. They are *not* measured in months, in ISO strings, or in any other unit of your choice. Whenever you enter *anything* that Calc should convert to a date, it *must* convert it to "the number of *days* elapsed from a start date called the epoch". It means that your second argument about false precision is wrong premise, too. Closing NOTABUG. Please don't reopen based on arbitrary citations from unrelated places. Thanks. [1] https://docs.oasis-open.org/office/OpenDocument/v1.3/os/part4-formula/OpenDocument-v1.3-os-part4-formula.html#__RefHeading__1017888_715980110
I'm not sure I'd call the international standard for dates "arbitrary" in a bug report about dates. Especially as you yourself said: > there is a definition that is used in the industry Yes, and it's ISO8601; certainly that's what I use in all the software I write. The bug therefore is in the ODF standard. > It means that your second argument about false precision is wrong premise, too. Nothing personal, but you don't seem to understand the concept of false precision. It's a very real problem and one that the date behaviour of ODF is going to exacerbate. I've seen countless occasions where some arbitrary value has been used for "no data" (the -01 day in this case), only to have that value then used as part of the calculation by someone else who didn't know it was a "no data" value. You may wish to read this: https://aucerna.com/blog/logical-fallacy-how-false-precision-affects-business-planning/ By forcing an arbitrary precision ODF is definitely going to be creating problems for its users.
(In reply to buggymcbug from comment #7) ISO 8601 is not relevant in this discussion. And false premise that you have is that you try to use very generic term "date" as used in many areas of human life, which is largely the topic handled by the ISO 8601, in the discussion of a very specific thing, that is *content of a cell in a spreadsheet*. No, there is *no* "bug in the ODF standard" because of "incompliance" with the ISO 8601. The date in spreadsheet is not just anything that is called "date" by human. It is *by definition* a type of data in a spreadsheet cell; it is defined to be a number; it is specifically defined what that number must designate (a number of days from a specific point); and it has its specific requirements as the rationale for this definition. It deliberately ignores any unrelated notions of human dates, like eras, years, leap seconds, shifts, etc. It is serving a specific goal: provide simple, efficient, and unambiguous way of computations in a mixed environment - where one may use simple numbers in the formula involving dates. It uses proleptic Gregorian calendar for all the covered date range; it has a limited such range (which would be another "incompliance" with general notion of date/time, but is just OK for a spreadsheet standard). And trying to reopen what you had been answered already, trying to push your "I want to use my personal (or even universally human) notion of date in the spreadsheet" is not going to change the fact that this is not a bug. However, I am not inclined to play games here. Let it stay reopened, as the monument for the status change war.
The data in the attached document are not dates, they are arbitrary text strings that resemble a YYYY-MM notation, but that has no effect on interpreting those text strings when pulling them down. Incrementing any foobar-number string increments the number. The cell content *is not* a date (a date would be one specific day of a specific month in a specific year), no matter what number format you apply. Even if you argue with ISO 8601 it would not be a date because any YYYY-MM would not be a date but a date range of the first to last day of the month. If you want your values be treated as dates then enter 2019-10-01 and 2019-11-01 and formatting them with YYYY-MM will display 2019-10 and 2019-11 and pulling those two dates down will recognize the one month difference and produce 2019-12-01 and 2020-01-01.