https://bugs.documentfoundation.org/show_bug.cgi?id=170339
--- Comment #3 from Sebastian <[email protected]> --- Thanks for your very fast reply and the related bug reports. Whether it is a bug in the code is debatable, there are arguments for both positions. Still, I am under the impression that there are two root causes for having repeated bug reports on this topic: 1) A kind of "historic" inconsistency between Calc on the one hand and [Microsoft/Apple/Google] on the other hand. 2) A docu which is not helpful when analyzing the different results because it suggests (and in some places explicitly says) that the result should be identical to Excel's. Your question: "It is not clear in the description, which file format is used ..." Both, in Excel and Calc, I started with a new table and manually entered the formula here. Today I did the same with Apple Numbers and Google sheets, which both yield results consistent with Excel. Related to topic 1) Your suggestion: "please report it to the ODF Technical Committee." Indeed, as stated in https://bugs.documentfoundation.org/show_bug.cgi?id=112320#c14, the clarity of the ODS spec is debateable: "Semantics: Returns the number of the week in the year for the given date." [https://docs.oasis-open.org/office/OpenDocument/v1.4/OpenDocument-v1.4-part4-formula.html#__RefHeading__1018212_715980110] The spec might be interpreted in both ways, the Excel/Notes/Sheets and the Calc way. I would say, Excel focuses on the term "week in the year", so Dec,31st is the 53rd week _in the year_, Calc focuses on assigning each week to exactly one calendar week number. After reading the semantics many times, I tend towards the Excel definition: If I do a query about year 2025, Excel calculates "number of the week _in the year_" (literally the year which I handed over as argument); Calc calculates the number of the week in "the next year". I did not find a bug tracker or so for the ODF, to I simply write a mail to the committee, I guess... ? If I can believe this report, https://www.verifiedmarketresearch.com/product/spreadsheet-software-market/, the Market share of MS-Excel, Google-Sheets, and Apple-Notes are >90% together. This is just a thought though and not a logical argument for or against a certain interpretation. Related to topic 2) I learned from the bug reports you provided, that I am not the first user stumbling over it. Therefore, I think irrespective what comes or might come out from the clarification with the ODF TC, the help of LibreOffice should be updated to match to what is implemented today. This would be efficient for both, users and the developers. A) Function definition WEEKNUM https://help.libreoffice.org/latest/en-US/text/scalc/01/func_weeknum.html A1) Cite {"as defined in ODF OpenFormula and compatible with other spreadsheet applications."} In several user "bug" reports, the problem with the ODF spec is mentioned. Stating that one aspect of the spec is subject to debate and what our interpretation is would be more helpful for the user than stating compatibility (silently implying that one's own interpretation alternative is the right one). A2) Cite "compatible with other". If we already know that >90% of the market share of spreadsheet applications are not compatible to the LibreOffice interpretation, this statement may be misleading to most spreadsheet application users. ad A1+A2: My proposal is to add: "In System 1, though, LibreOffice is not compatible to MS Excel, Apple Numbers or Google Sheets due to a different interpretation of how to assign the last days of a year. A2) The examples further down on the page only contain examples of the start of the year. When investigating the issue, it may have helped me to have the following example, too "=WEEKNUM(DATE(2000;12;31);1) returns 1. Note that commercial applications such as Excel, Sheets or Notes return 54; LibreOffice adopts a different interpretation of the ODS spec." B) Wiki WEEKNUM_EXCEL2003 https://wiki.documentfoundation.org/Documentation/Calc_Functions/WEEKNUM_EXCEL2003 "interoperability with legacy documents created with _older_ versions of Microsoft Excel." B1) As pointed out in some of the bug reports you cited, the "Excel 2003 interpretation" also holds in Excel 2016 and, as in my tests, Excel 365 MSO (Version 2502). During my investigations, I came across this help page but I concluded that this page/this function is not applicable to my problem since I was comparing to the newest version of Excel. It should be changed to "interoperability with legacy documents created with certain versions of Microsoft Excel (including newer ones such as 365 2502)" Hint: this aspect is not covered by https://bugs.documentfoundation.org/show_bug.cgi?id=168584, since that one only addresses the code, not the docu. B2) "For all _new documents_ and most cases that do not require interoperability with _legacy_ Excel spreadsheets, it is recommended that you use Calc’s two more flexible functions to calculate the week number of a specified date. These are WEEKNUM". Same. I reasoned, since I am using the newest version of Excel, Calc's and Excel's WEEKNUM would yield the same result. From the cited bug reports, I later learned that they are known not to do so. "These are WEKNUM (compliant with ODF 1.2)" Since the difference addressed by WEEKNUM_EXCEL2003 is related to an ambiguous interpretation in the ODF spec, the statement "compliant with..." is misleading because it does not openly address the inconsistency which is already known. C) Function definition WEEKNUM_EXCEL2003 https://help.libreoffice.org/latest/en-US/text/scalc/01/func_weeknumadd.html?&DbPAR=SHARED&System=UNIX "The WEEKNUM_EXCEL2003 function is designed to calculate week numbers exactly as Microsoft Excel 2003 did. Use the WEEKNUM function for ODF OpenFormula and Excel 2010 compatibility" This statement is not really wrong, but adding that newer versions of Excel behave like EXCEL2003 may help a lot. -- You are receiving this mail because: You are the assignee for the bug.
