Bug 139421 - Calc: calcuate weeknumber-year missing in 2021
Summary: Calc: calcuate weeknumber-year missing in 2021
Status: UNCONFIRMED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: All All
: medium enhancement
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
: 33801 (view as bug list)
Depends on:
Blocks: Calc-Function
  Show dependency treegraph
 
Reported: 2021-01-05 07:11 UTC by j.chansit
Modified: 2021-07-04 06:11 UTC (History)
5 users (show)

See Also:
Crash report or crash signature:


Attachments
Spreadsheet showing incorrect values of Weeknum (17.45 KB, application/vnd.oasis.opendocument.spreadsheet)
2021-01-05 11:40 UTC, Peter S Anderson
Details

Note You need to log in before you can comment on or make changes to this bug.
Description j.chansit 2021-01-05 07:11:55 UTC
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
Comment 1 Mike Kaganski 2021-01-05 07:29:00 UTC
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?
Comment 2 Peter S Anderson 2021-01-05 11:40:37 UTC Comment hidden (obsolete)
Comment 3 Regina Henschel 2021-01-05 12:12:17 UTC
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
Comment 4 Mike Kaganski 2021-01-05 12:58:49 UTC
(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
Comment 5 Regina Henschel 2021-01-05 13:21:41 UTC
(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?
Comment 6 Mike Kaganski 2021-01-05 13:39:13 UTC
(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
Comment 7 Robert Lacroix 2021-01-06 05:31:10 UTC
(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.
Comment 8 Mike Kaganski 2021-01-06 06:09:19 UTC
(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" :-)
Comment 9 Mike Kaganski 2021-01-06 06:45:55 UTC
(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
Comment 10 Eike Rathke 2021-01-08 18:41:02 UTC
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.
Comment 11 Eike Rathke 2021-01-08 18:45:23 UTC
Whatever, this would need an ODF extension and couldn't be stored in Excel file formats at all, losing information.
Comment 12 Eike Rathke 2021-01-18 14:57:09 UTC
*** Bug 33801 has been marked as a duplicate of this bug. ***
Comment 13 Mike Kaganski 2021-01-18 15:09:25 UTC
(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
Comment 14 Mike Kaganski 2021-01-18 15:18:17 UTC
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?
Comment 15 Robert Lacroix 2021-01-18 15:48:24 UTC
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!
Comment 16 Eike Rathke 2021-01-20 15:14:32 UTC
(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.
Comment 17 Eike Rathke 2021-01-20 15:22:24 UTC
(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).
Comment 18 Mike Kaganski 2021-01-20 18:57:20 UTC
(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?