Description: result from function text(date-value,"yyyy-WW") is missing when date-value = 31/12/2020 result = 2020-1 missing value!!! it correct value should 2021-1 Steps to Reproduce: 1.at a1 = 31/12/2020 2. b1 =text(a1,"yyyy-WW") 3. Actual Results: 2020-1 Expected Results: 2021-1 Reproducible: Always User Profile Reset: No Additional Info: Version: 7.0.4.2 Build ID: 00(Build:2) CPU threads: 8; OS: Linux 5.4; UI render: default; VCL: gtk3 Locale: en-GB (en_US.UTF-8); UI: en-US Ubuntu package version: 1:7.0.4_rc2-0ubuntu0.20.04.2 Calc: threaded
I'm unsure if this is a bug. The 'YYYY' format code does not (and IMO should not) change depending on if the format string includes WW or not. What if the code be 'YYYY-MM-DD-WW'? What should be considered for YYYY value then, the MM-DD or WW? Possibly another separate code is needed, like "calendar week year" or so?
Created attachment 168700 [details] Spreadsheet showing incorrect values of Weeknum I have confirmed this bug in 6.4.7.2 and DEV although the results given in the initial bug report are incorrect. The bug notes that the text formatting function, text(date-value, “WW”) is giving the incorrect result for the date 31/12/2020. My reference documentation is https://help.libreoffice.org/3.3/Calc/WEEKNUM Here it states that week number 1 of any year is the week that contains January 4th. The problem is that Jan 4 2020 was a Saturday and so Jan 5 2020 was the start of week 2 of 2020 and week 53 starts on Dec 27 and ends on Jan 2. Week 1 of 2021 starts on Jan 3, I have attached a spreadsheet that shows that there is a problem with both the Weeknum function and the WW format from Dec 27 2020 and probably for most of 2021.
The function WEEKNUM has a second parameter to determine the mode of calculation. It defaults to 1, which is not the ISO way. To get the ISO calculation you have to use parameter 21. Or you use function ISOWEEKNUM. Please read help for WEEKNUM and ISOWEEKNUM of a current release. I cannot confirm the initial reported results =TEXT(A1;"yyyy-WW") returns correctly the ISO week number 2020-53 for date 2020-12-31. 2021-53 for dates 2021-01-01, 2021-01-02, 2021-01-03 2021-1 for dates 2021-01-04 (Monday) to 2021-01-10 (Sunday) Tested with Version: 7.0.0.2 (x64) Build ID: c01aa64b6c3d89ebe5fe69c28c7adb24eb85249c CPU threads: 8; OS: Windows 10.0 Build 19041; UI render: Skia/Raster; VCL: win Locale: de-DE (en_US); UI: en-US Calc: CL and Version: 7.2.0.0.alpha0+ (x64) Build ID: 90668f3473f4e52cec823ad39c6fcb44ba7c089b CPU threads: 8; OS: Windows 10.0 Build 19041; UI render: default; VCL: win Locale: de-DE (en_US); UI: en-US Calc: CL
(In reply to Regina Henschel from comment #3) > I cannot confirm the initial reported results > =TEXT(A1;"yyyy-WW") returns correctly the ISO week number > 2020-53 for date 2020-12-31. This depends on language selected in Format Cells->Numbers. For e.g. ru-RU or en-US, this gives 2020-1. For de-DE, it returns 2020-53. > 2021-53 for dates 2021-01-01, 2021-01-02, 2021-01-03 This is exactly what OP complains about: the week number 53 should be *of year 2020* for dates in the beginning of year 2021, if use de-DE; or week 1 of *year 2021* is needed for dates in the end of 2020 with en-US. Note the "[YYYY] indicates the ISO week-numbering year which is slightly different from the traditional Gregorian calendar year" in [1]. https://en.wikipedia.org/wiki/ISO_8601#Week_dates
(In reply to Mike Kaganski from comment #4) > (In reply to Regina Henschel from comment #3) > > I cannot confirm the initial reported results > > =TEXT(A1;"yyyy-WW") returns correctly the ISO week number > > 2020-53 for date 2020-12-31. > > This depends on language selected in Format Cells->Numbers. For e.g. ru-RU > or en-US, this gives 2020-1. For de-DE, it returns 2020-53. > > > 2021-53 for dates 2021-01-01, 2021-01-02, 2021-01-03 > > This is exactly what OP complains about: the week number 53 should be *of > year 2020* for dates in the beginning of year 2021, if use de-DE; or week 1 > of *year 2021* is needed for dates in the end of 2020 with en-US. > > Note the "[YYYY] indicates the ISO week-numbering year which is slightly > different from the traditional Gregorian calendar year" in [1]. > > https://en.wikipedia.org/wiki/ISO_8601#Week_dates Your are right. That means, that LibreOffice needs a new format string "YYYY-Www" or "YYYYWww" to indicate the ISO way of calculating? Something for Eike?
(In reply to Regina Henschel from comment #5) > That means, that LibreOffice needs a new format string > "YYYY-Www" or "YYYYWww" to indicate the ISO way of calculating? I'd suggest something like "YW" (for "week-numbering year"), which is not yet used in our number format codes [1]. Note that we should not limit us to "ISO way", because ISO week-numbering year is just a special case of week-numbering year. But then, how to separate "week-numbering year in current locale convention" from "ISO week-numbering year", esp. if the format code has no other date parts that could hint that ISO convention is used? ... so maybe two codes needed ... > Something for Eike? ... which is why I CCed him when I wrote in comment #1: > Possibly another separate code is needed, like "calendar week year" or so? [1] https://help.libreoffice.org/latest/en-US/text/shared/01/05020301.html
(In reply to Regina Henschel from comment #3) > The function WEEKNUM has a second parameter to determine the mode of > calculation. It defaults to 1, which is not the ISO way. To get the ISO > calculation you have to use parameter 21. Or you use function ISOWEEKNUM. > Please read help for WEEKNUM and ISOWEEKNUM of a current release. > > I cannot confirm the initial reported results > =TEXT(A1;"yyyy-WW") returns correctly the ISO week number > 2020-53 for date 2020-12-31. > 2021-53 for dates 2021-01-01, 2021-01-02, 2021-01-03 > 2021-1 for dates 2021-01-04 (Monday) to 2021-01-10 (Sunday) Is that a typo TEXT value with the middle row of dates? The year-weeknum is non-monotonic for increasing dates. 2021-53 should come after 2021-1 in a sequence of successive dates. The OP is asking for a WEEKNUM mode from "system 1", which is any mode except the ISO mode. System 1 always puts January 1 in week 1, and the preceding December days in the same week are also in week 1 (of the following calendar year). The day you select as the start of the week determines the particular WEEKNUM mode. For symmetry with ISO mode, I would start the week on Monday, so use WEEKNUM mode 2 (or 11). The question arose on how to select a mode/format with the TEXT function. There are 4 options: 1) add a new format code to TEXT was suggested. It's legacy compatible. 2) add a document option. It would be buried in options, so not nice to use. 3) add an optional argument to TEXT, a-la WEEKNUM; default ISO mode for legacy compatibility? 4) make meaning of current format depend on locale. Undesireable, causes global side effects if I need to change locale for one spreadsheet cell. I would select option 3. It's compatible, more flexible, and the value displayed with TEXT in one cell can be easily made to match any integer result of WEEKNUM in another cell.
(In reply to Robert Lacroix from comment #7) > 4) make meaning of current format depend on locale. Undesireable, causes > global side effects if I need to change locale for one spreadsheet cell. It *already* *is* dependent on locale - see comment 4. And no, "any mode except the ISO mode" is not equal to "system 1" :-)
(In reply to Robert Lacroix from comment #7) > The question arose on how to select a mode/format with the TEXT function. No, this question is more general: how to select it *in any format string*. The format strings are used not only in TEXT spreadsheet function [1], but also in other places: Format function in Basic [2]; cell format in Calc and Writer; Field format in Writer [3]... And any of them might need this. > There are 4 options: > 1) add a new format code to TEXT was suggested. It's legacy compatible. > 2) add a document option. It would be buried in options, so not nice to use. Its discoverability is just the lesser of the problems of this option. The greater one is that it would make it impossible to use different systems in different places, e.g. when creating multilanguage documents with different systems used in different parallel sections. > 3) add an optional argument to TEXT, a-la WEEKNUM; default ISO mode for > legacy compatibility? This is absolutely not an option - see "not only in TEXT spreadsheet function, but also in other places" above. > 4) make meaning of current format depend on locale. Undesireable, causes > global side effects if I need to change locale for one spreadsheet cell. This option could likely be the best is we create some NatNum modifier associated with ISO mode? Then no need in solution #1. [1] https://help.libreoffice.org/latest/en-US/text/scalc/01/04060110.html [2] https://help.libreoffice.org/latest/en-US/text/sbasic/shared/03120301.html [3] https://help.libreoffice.org/7.1/en-US/text/shared/01/05020300.html
Coming up with an arbitrary made-up format code like YW or WWW is fragile as you never know if Excel doesn't come along with a different definition of the same later.. Abusing a [NatNum#] modifier for this also isn't appealing. However, we could introduce a new modifier [WeekNum=#] where # can take either * the same values as the WEEKNUM() Mode parameter * benefit: the same values user might know * drawback: just those values or * values that define the minimum number of days in a week and the start day of the week, by which all week definitions can be described, which is what locale data uses * here the WEEKNUM() Mode values 11..17 could mean the same, MinimalDaysInFirstWeek=1 and StartDayOfWeek=1..7 * the WEEKNUM() Mode=1 US definition would be 17, MinimalDaysInFirstWeek=1 and StartDayOfWeek=7 (Sunday) (where generally MinimalDaysInFirstWeek=1 means the week containing January 1 is week 1) * the WEEKNUM() Mode=2 definition would be 11, MinimalDaysInFirstWeek=1 and StartDayOfWeek=1 (Monday) * the ISO definition would be 41, MinimalDaysInFirstWeek=4 and StartDayOfWeek=1 (Monday) * benefit: all possible week definitions * drawback: user might have a harder time figuring out, may need an extra help page I tend to the latter because of its flexibility and so we're not stuck with the definition of WEEKNUM(). Though in practice (locale data) I haven't seen definitions using other than StartDayOfWeek={mon,sat,sun} and MinimalDaysInFirstWeek={1,4,7}; these already are not all representable with the WEEKNUM() Mode definition.
Whatever, this would need an ODF extension and couldn't be stored in Excel file formats at all, losing information.
*** Bug 33801 has been marked as a duplicate of this bug. ***
(In reply to Eike Rathke from comment #10) > I tend to the latter because of its flexibility and so we're not stuck with > the definition of WEEKNUM(). +1
But what would be the resulting format string look like with the [WeekNum] modifier, which would display the week-year? Will it (ab)use the YYYY code for week-year then?
I like the approach with greater flexibility, so not being stuck with the definition of WEEKNUM. But users would still have a problem deciding how to make the function result match the formatted string. To parry this issue, how about a new WEEKNUM-type function that matches a given formatting code (whatever that turns out to be). There are already 4 weeknum functions (WEEKNUM, ISOWEEKNUM, WEEKNUM_OOO, WEEKNUM_EXCEL2003) so there can't be an objection that there would be too many; none of them does what we want!
(In reply to Mike Kaganski from comment #14) > But what would be the resulting format string look like with the [WeekNum] > modifier, which would display the week-year? Will it (ab)use the YYYY code > for week-year then? No. Any WW format code would use the rule set by [WeekNum=#] if present. For example WW => displays week number as per the number format's locale's rule, as it is currently the case. [WeekNum=41]WW => displays ISO week.
(In reply to Robert Lacroix from comment #15) > To parry this issue, how about a new WEEKNUM-type function that matches a > given formatting code (whatever that turns out to be). Doesn't seem to be a good idea to me. First, it would not work if its argument was not a cell reference, i.e. all calculated arguments would lack the necessary information. Second, it would have to recalculate if a cell format changed, display format changes *never* lead to recalculation (and you actually don't want that).
(In reply to Eike Rathke from comment #16) > No. Any WW format code would use the rule set by [WeekNum=#] if present. > [WeekNum=41]WW => displays ISO week. That's fine, but the issue is not (only) about week number, but about the year of that week, which is different from normal year?
Moving to NEW based on comment 10