Rethinking... One statement is not true "(however, which is however... "
In Excel, the span of a week can have two calendar weeks assigned (one for the current year, one for the next year), in LibreOffice the switch happens at the indicated starting day of the week, so each span of a week has a unique calendar week assigned. Still, the Excel solution is more helpful in upcounting weeks.

There are a lot of reports about WEEKNUM calculations, e.g. bug 112320, bug 139342, bug 139548, bug 162598.

If the spreadsheet was exported to *.ods by Excel, we cannot do anything but need to follow the ODF specification. If you think, that the ODF specification needs improvements, please report it to the ODF Technical Committee.

If we import an *.xlsx file, we could use the WEEKNUM_EXCEL2003 function. That does not happen currently. This problem is already tracked in bug 168584.

It is not clear in the description, which file format is used and whether the file was created from scratch in LibreOffice or created by another application and opened in LibreOffice.

Reading the comments in the above listed bug reports, I suggest to close this one as NOTABUG too.


If compatibility is necessary, use ISOWEEKNUM, both in Excel and LibreOffice.

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.