Description: The function WEEKNUM is not returning the correct result for the last days of the year when January 1st (of next year) is not a Sunday. If December 31st is not a Saturday, WEEKNUM(YEAR, 12, 31) returns "1". This affects other days of the last week of December. For example, for WEEKNUM(YEAR, 12, 29) to return the correct result, the 29th has to be a Thursday, a Friday or a Saturday, independently of the day of the 31st. Steps to Reproduce: 1. On a row input the years 2015 through 2023 2. Next row: DATE(cell above, 12, 31) for each column 3. Next row: WEEKDAY(cell above) for each column 4. Next row: WEEKNUM(reference DATE row) for each column Actual Results: 2023 2022 2021 2020 2019 2018 2017 2016 2015 12/31/23 12/31/22 12/31/21 12/31/20 12/31/19 12/31/18 12/31/17 12/31/16 12/31/15 1 7 6 5 3 2 1 7 5 1 53 1 1 1 1 1 53 1 Expected Results: 2023 2022 2021 2020 2019 2018 2017 2016 2015 12/31/2023 12/31/2022 12/31/2021 12/31/2020 12/31/2019 12/31/2018 12/31/2017 12/31/2016 12/31/2015 1 7 6 5 3 2 1 7 5 53 53 53 53 53 53 53 53 53 Reproducible: Always User Profile Reset: No Additional Info: Version: 24.8.0.3 (X86_64) / LibreOffice Community Build ID: 0bdf1299c94fe897b119f97f3c613e9dca6be583 CPU threads: 16; OS: Linux 6.9; UI render: default; VCL: gtk3 Locale: en-US (en_US.UTF-8); UI: en-US Flatpak Calc: threaded
Why exactly do you think that your "expected results" are the correct ones? As far as I can see, the "actual results" seem to be correct, at least for ODS files.
I do not understand what you mean. If the 31st of December is in the last week of the year, WEEKNUM should say 52 or 53, not 1. Moreover, plenty of functions have defaults to be compatible with Excel and Docs. I have tested this in both, as well as in Calc, and the expected result is not 1.
(In reply to Fred from comment #2) > If the 31st of December is in the last week of the year, WEEKNUM should say > 52 or 53, not 1. That's incorrect. Please read the Help content about this function. > > Moreover, plenty of functions have defaults to be compatible with Excel and > Docs. I have tested this in both, as well as in Calc, and the expected > result is not 1. Maybe you mean to use a different function in Calc? Perhaps WEEKNUM_EXCEL2003(), or ISOWEEKNUM(), or... Are we talking about an ods file? Or is it about an xls(x) file? Are the results different when saving and reloading with different formats?
>That's incorrect. It's literally not. That's how it works everywhere else, regardless of file format or anything. I was under the assumption that there was an interest in keeping features consistent between systems as to allow people to migrate with as little tinkering as possible. I can see I was wrong in that regard, at least. I apologize. I will not pursue this further and go back to using other programs that behave consistently and whose community is not condescending.
@Fred, I understand your reaction, although there was no intention to be/sound condescending. My impression is that you did not check the Help page about WEEKNUM that I suggested: <https://help.libreoffice.org/24.8/en-US/text/scalc/01/func_weeknum.html> There is indeed a difference between how Calc calculates WEEKNUM() and how other spreadsheet tools do it. Calc uses the ODF standard. Other tools might not follow it, or might interpret it differently, or might use a different implementation. There might be some bug when exporting to XLSX format; I have not checked.
I tested in office.com's Excel and it outputs by default your expected "53 for all" result. Per the suggestion from ady, testing reveals that the same can be achieved in Calc with =WEEKNUM_EXCEL2003(A2;1) (In reply to Fred from comment #4) > I can see I was wrong in that regard, at least. I apologize. I will not > pursue this further and go back to using other programs that behave > consistently and whose community is not condescending. I am unable to locate condescension in ady's comments. Please keep an open mind when reading replies from others. As explained in the help for WEEKNUM_EXCEL2003: "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, or ISOWEEKNUM function when you just need ISO 8601 week numbers." So Excel in 2024 actually still calculates as it did in 2003. Indeed, testing with Excel's ISOWEEKNUM function I get the exact same result as Calc's ISOWEEKNUM. https://support.microsoft.com/en-us/office/isoweeknum-function-1c2d0afe-d25b-4ab1-8894-8d0520e90e0e https://support.microsoft.com/en-us/office/weeknum-function-e5c43a03-b4ab-426c-b411-b18c13c75340