[Libreoffice-bugs] [Bug 139421] Calc: calcuate weeknumber-year missing in 2021
https://bugs.documentfoundation.org/show_bug.cgi?id=139421 Xisco Faulí changed: What|Removed |Added Ever confirmed|0 |1 CC||xiscofa...@libreoffice.org Status|UNCONFIRMED |NEW --- Comment #19 from Xisco Faulí --- Moving to NEW based on comment 10 -- You are receiving this mail because: You are the assignee for the bug.
[Libreoffice-bugs] [Bug 139421] Calc: calcuate weeknumber-year missing in 2021
https://bugs.documentfoundation.org/show_bug.cgi?id=139421 Mike Kaganski changed: What|Removed |Added See Also||https://bugs.documentfounda ||tion.org/show_bug.cgi?id=71 ||170 -- You are receiving this mail because: You are the assignee for the bug.___ Libreoffice-bugs mailing list Libreoffice-bugs@lists.freedesktop.org https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs
[Libreoffice-bugs] [Bug 139421] Calc: calcuate weeknumber-year missing in 2021
https://bugs.documentfoundation.org/show_bug.cgi?id=139421 Eike Rathke changed: What|Removed |Added OS|Linux (All) |All Hardware|x86-64 (AMD64) |All -- You are receiving this mail because: You are the assignee for the bug.___ Libreoffice-bugs mailing list Libreoffice-bugs@lists.freedesktop.org https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs
[Libreoffice-bugs] [Bug 139421] Calc: calcuate weeknumber-year missing in 2021
https://bugs.documentfoundation.org/show_bug.cgi?id=139421 Eike Rathke changed: What|Removed |Added Version|7.0.4.2 release |Inherited From OOo -- You are receiving this mail because: You are the assignee for the bug.___ Libreoffice-bugs mailing list Libreoffice-bugs@lists.freedesktop.org https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs
[Libreoffice-bugs] [Bug 139421] Calc: calcuate weeknumber-year missing in 2021
https://bugs.documentfoundation.org/show_bug.cgi?id=139421 Eike Rathke changed: What|Removed |Added Severity|normal |enhancement -- You are receiving this mail because: You are the assignee for the bug.___ Libreoffice-bugs mailing list Libreoffice-bugs@lists.freedesktop.org https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs
[Libreoffice-bugs] [Bug 139421] Calc: calcuate weeknumber-year missing in 2021
https://bugs.documentfoundation.org/show_bug.cgi?id=139421 --- Comment #17 from Eike Rathke --- (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). -- You are receiving this mail because: You are the assignee for the bug.___ Libreoffice-bugs mailing list Libreoffice-bugs@lists.freedesktop.org https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs
[Libreoffice-bugs] [Bug 139421] Calc: calcuate weeknumber-year missing in 2021
https://bugs.documentfoundation.org/show_bug.cgi?id=139421 --- Comment #16 from Eike Rathke --- (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 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. -- You are receiving this mail because: You are the assignee for the bug.___ Libreoffice-bugs mailing list Libreoffice-bugs@lists.freedesktop.org https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs
[Libreoffice-bugs] [Bug 139421] Calc: calcuate weeknumber-year missing in 2021
https://bugs.documentfoundation.org/show_bug.cgi?id=139421 --- Comment #15 from Robert Lacroix --- 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! -- You are receiving this mail because: You are the assignee for the bug.___ Libreoffice-bugs mailing list Libreoffice-bugs@lists.freedesktop.org https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs
[Libreoffice-bugs] [Bug 139421] Calc: calcuate weeknumber-year missing in 2021
https://bugs.documentfoundation.org/show_bug.cgi?id=139421 --- Comment #14 from Mike Kaganski --- 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 code for week-year then? -- You are receiving this mail because: You are the assignee for the bug.___ Libreoffice-bugs mailing list Libreoffice-bugs@lists.freedesktop.org https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs
[Libreoffice-bugs] [Bug 139421] Calc: calcuate weeknumber-year missing in 2021
https://bugs.documentfoundation.org/show_bug.cgi?id=139421 --- Comment #13 from Mike Kaganski --- (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 -- You are receiving this mail because: You are the assignee for the bug.___ Libreoffice-bugs mailing list Libreoffice-bugs@lists.freedesktop.org https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs
[Libreoffice-bugs] [Bug 139421] Calc: calcuate weeknumber-year missing in 2021
https://bugs.documentfoundation.org/show_bug.cgi?id=139421 Eike Rathke changed: What|Removed |Added CC||seu...@gmail.com --- Comment #12 from Eike Rathke --- *** Bug 33801 has been marked as a duplicate of this bug. *** -- You are receiving this mail because: You are the assignee for the bug.___ Libreoffice-bugs mailing list Libreoffice-bugs@lists.freedesktop.org https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs
[Libreoffice-bugs] [Bug 139421] Calc: calcuate weeknumber-year missing in 2021
https://bugs.documentfoundation.org/show_bug.cgi?id=139421 --- Comment #11 from Eike Rathke --- Whatever, this would need an ODF extension and couldn't be stored in Excel file formats at all, losing information. -- You are receiving this mail because: You are the assignee for the bug.___ Libreoffice-bugs mailing list Libreoffice-bugs@lists.freedesktop.org https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs
[Libreoffice-bugs] [Bug 139421] Calc: calcuate weeknumber-year missing in 2021
https://bugs.documentfoundation.org/show_bug.cgi?id=139421
--- Comment #10 from Eike Rathke ---
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.
--
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs
[Libreoffice-bugs] [Bug 139421] Calc: calcuate weeknumber-year missing in 2021
https://bugs.documentfoundation.org/show_bug.cgi?id=139421 Roman Kuznetsov <79045_79...@mail.ru> changed: What|Removed |Added Blocks||108827 Referenced Bugs: https://bugs.documentfoundation.org/show_bug.cgi?id=108827 [Bug 108827] [META] Calc functions bugs and enhancements -- You are receiving this mail because: You are the assignee for the bug.___ Libreoffice-bugs mailing list Libreoffice-bugs@lists.freedesktop.org https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs
[Libreoffice-bugs] [Bug 139421] Calc: calcuate weeknumber-year missing in 2021
https://bugs.documentfoundation.org/show_bug.cgi?id=139421 --- Comment #9 from Mike Kaganski --- (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 -- You are receiving this mail because: You are the assignee for the bug.___ Libreoffice-bugs mailing list Libreoffice-bugs@lists.freedesktop.org https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs
[Libreoffice-bugs] [Bug 139421] Calc: calcuate weeknumber-year missing in 2021
https://bugs.documentfoundation.org/show_bug.cgi?id=139421 --- Comment #8 from Mike Kaganski --- (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" :-) -- You are receiving this mail because: You are the assignee for the bug.___ Libreoffice-bugs mailing list Libreoffice-bugs@lists.freedesktop.org https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs
[Libreoffice-bugs] [Bug 139421] Calc: calcuate weeknumber-year missing in 2021
https://bugs.documentfoundation.org/show_bug.cgi?id=139421 --- Comment #7 from Robert Lacroix --- (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;"-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. -- You are receiving this mail because: You are the assignee for the bug.___ Libreoffice-bugs mailing list Libreoffice-bugs@lists.freedesktop.org https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs
[Libreoffice-bugs] [Bug 139421] Calc: calcuate weeknumber-year missing in 2021
https://bugs.documentfoundation.org/show_bug.cgi?id=139421 --- Comment #6 from Mike Kaganski --- (In reply to Regina Henschel from comment #5) > That means, that LibreOffice needs a new format string > "-Www" or "Www" 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 -- You are receiving this mail because: You are the assignee for the bug.___ Libreoffice-bugs mailing list Libreoffice-bugs@lists.freedesktop.org https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs
[Libreoffice-bugs] [Bug 139421] Calc: calcuate weeknumber-year missing in 2021
https://bugs.documentfoundation.org/show_bug.cgi?id=139421 --- Comment #5 from Regina Henschel --- (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;"-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 "[] 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 "-Www" or "Www" to indicate the ISO way of calculating? Something for Eike? -- You are receiving this mail because: You are the assignee for the bug.___ Libreoffice-bugs mailing list Libreoffice-bugs@lists.freedesktop.org https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs
[Libreoffice-bugs] [Bug 139421] Calc: calcuate weeknumber-year missing in 2021
https://bugs.documentfoundation.org/show_bug.cgi?id=139421 --- Comment #4 from Mike Kaganski --- (In reply to Regina Henschel from comment #3) > I cannot confirm the initial reported results > =TEXT(A1;"-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 "[] 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 -- You are receiving this mail because: You are the assignee for the bug.___ Libreoffice-bugs mailing list Libreoffice-bugs@lists.freedesktop.org https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs
[Libreoffice-bugs] [Bug 139421] Calc: calcuate weeknumber-year missing in 2021
https://bugs.documentfoundation.org/show_bug.cgi?id=139421 Regina Henschel changed: What|Removed |Added CC||rb.hensc...@t-online.de --- Comment #3 from Regina Henschel --- 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;"-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 -- You are receiving this mail because: You are the assignee for the bug.___ Libreoffice-bugs mailing list Libreoffice-bugs@lists.freedesktop.org https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs
[Libreoffice-bugs] [Bug 139421] Calc: calcuate weeknumber-year missing in 2021
https://bugs.documentfoundation.org/show_bug.cgi?id=139421 --- Comment #2 from Peter S Anderson --- Created attachment 168700 --> https://bugs.documentfoundation.org/attachment.cgi?id=168700=edit 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. -- You are receiving this mail because: You are the assignee for the bug.___ Libreoffice-bugs mailing list Libreoffice-bugs@lists.freedesktop.org https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs
[Libreoffice-bugs] [Bug 139421] Calc: calcuate weeknumber-year missing in 2021
https://bugs.documentfoundation.org/show_bug.cgi?id=139421 Mike Kaganski changed: What|Removed |Added CC||er...@redhat.com --- Comment #1 from Mike Kaganski --- I'm unsure if this is a bug. The '' format code does not (and IMO should not) change depending on if the format string includes WW or not. What if the code be '-MM-DD-WW'? What should be considered for value then, the MM-DD or WW? Possibly another separate code is needed, like "calendar week year" or so? -- You are receiving this mail because: You are the assignee for the bug.___ Libreoffice-bugs mailing list Libreoffice-bugs@lists.freedesktop.org https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs
