https://bugs.documentfoundation.org/show_bug.cgi?id=170806
--- Comment #2 from ady <[email protected]> --- TY for replying. (In reply to Regina Henschel from comment #1) > I agree, that having five m in the ODF specification is wrong. I have > classified it as editorial error and have added it to our list in > https://github.com/oasis-tcs/odf-tc/issues/55#issuecomment-3904345386 Please note that the table itself (Table 20) also has the same typo, 5 letters "m" instead of the correct 4. > From version 3.5 to 4.0 a "Date Acceptance Patterns" was introduced. The problem is, IMHO, a misinterpretation of the relation between the "Date Acceptance Pattern" and Calc's functions. IIUC, the "Date Acceptance Pattern" is there for the direct introduction of values that Calc should interpret as some form of DateTime, in order to use the adequate internal serial number in the cell and to display the relevant value in an adequate format. But arguments of functions (such as "Text D" for DATEVALUE()) have nothing to do with how the value / argument arrived to the cell, and also the latter has nothing to do with the parsing of those values by each function after they are already in the cell. IOW, the "Date Acceptance Pattern" should be relevant for the introduction of the values (or importing them or whatever), but should not be a condition to whether a function can parse whichever value ends up in the cell; that's up to the function, not the "Date Acceptance Pattern" setting. Let's take the pattern "DD MMM YYYY" for example. For en-us, this is not a default date accepted pattern, but it could be perfectly parsed by DATEVALUE() (always as "Text D" argument, not as a Date format with internal serial number). A cell containing Text such as "15 Feb 2026" has no internal serial number (i.e. the cell is Text, or it is General/Standard, but not a Date cell format), and DATEVALUE() should have no problem giving a correct accurate result, with its argument being either a reference to the cell containing such text, or as DATEVALUE("15 Feb 2026"). OTOH, let's take a pattern such as "MMMMM/DD/YYYY" (note the 5 letters "m"). For en-us, the order of this pattern matches the typical M/D/Y order. If you have a cell with the correct internal serial number, you could display the correct accurate date with that custom number format. But, if you have another, different cell with the Text "J/02/2026", DATEVALUE() will not be able to parse it accurately, because it does not know whether "J/02/2026" means the second day of the month of January, or June, or July of the year 2026. At most, it could have some heuristic of some form, but it will not be precise and accurate in each and every case. So, a cell with the text "15 Feb 2026" _should_ be a valid argument for DATEVALUE() when the cell in which that Text value is located has en-us locale. And, when the cell in which the DATEVALUE() function is located has en-us locale, then DATEVALUE("15 Feb 2026") should also be acceptable. Neither case should depend on the "Date Acceptance Pattern" setting. In contrast, DATEVALUE("J/02/2026") should not be accepted in such en-us cell, even when it matches the "Date Acceptance Pattern" for en-us. The same goes for VALUE(), except that the result is different than DATEVALUE(). DATEVALUE() should still be able to parse the whole Text that looks as DateTime too, but then disregards the Time part for the result, whereas VALUE() provides the complete internal serial value. Let's take another example, "01/02/2026". If cell A1 contains that Text (not formatted as Date, but as Text, or as General/Standard with an initial "hidden" apostrophe), and the same cell A1 is locale en-US, then DATEVALUE(A1) should give you the serial number for the second day of the month of January of the year 2026. But, if the locale of cell A1 matches a language-country that has the other default pattern (D/M/Y), then the same DATEVALUE(A1) should result in the internal serial number for the first day of the month of February of the year 2026. This should be independent of the "Date Acceptance Pattern" setting, which the user could had modified for whichever reason. The examples I provided are for en-US because ODF mentions en-US specifically, but the principles still apply to other locales. As long as the "Text D" argument can be parsed uniquely (with no ambiguity towards the exact internal serial number of the DateTime, and considering the locale of the cell in which the argument is located), the specific "datetime-like" pattern or the "Date Acceptance Pattern" settings should not matter. It is up to the specific function to adequately parse its argument. The dependency of DATEVALUE() and of VALUE() on the "Date Acceptance Pattern" setting should be decoupled, nullify, ended. -- You are receiving this mail because: You are the assignee for the bug.
