Download it now!
Bug 33801 - WW format returns wrong result compared correct weeknum() / 'locale setting' related
Summary: WW format returns wrong result compared correct weeknum() / 'locale setting' ...
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: All All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: Cell-Formula
  Show dependency treegraph
 
Reported: 2011-02-01 03:31 UTC by seumas
Modified: 2019-01-18 03:59 UTC (History)
6 users (show)

See Also:
Crash report or crash signature:


Attachments
sample document with WEEKNUM in mode 21 (ISO 8601) -- adjust Locale to test (9.74 KB, application/vnd.oasis.opendocument.spreadsheet)
2017-03-13 14:13 UTC, V Stuart Foote
Details

Note You need to log in before you can comment on or make changes to this bug.
Description seumas 2011-02-01 03:31:26 UTC
Example:

Set Language settings to English UK.

Enter 07/02/2011 (7th February) as the date in cell A1
Set A2 as =weeknum(A1,1)
Set A3 as =A1 and format cell as WW

A2 returns 6
A3 returns 7

Cell A2 (value 6) is correct for the UK. Cell A3 has the correct value for the US but incorrect for the UK.

Expected: it should return the same value for both when language settings are English UK.
Comment 1 Björn Michaelsen 2011-12-23 11:47:21 UTC Comment hidden (obsolete)
Comment 2 Florian Reisinger 2012-08-14 14:00:27 UTC Comment hidden (obsolete)
Comment 3 Florian Reisinger 2012-08-14 14:01:34 UTC Comment hidden (obsolete)
Comment 4 Florian Reisinger 2012-08-14 14:06:17 UTC Comment hidden (obsolete)
Comment 5 Florian Reisinger 2012-08-14 14:08:19 UTC Comment hidden (obsolete)
Comment 6 seumas 2012-08-14 14:25:45 UTC
This bug still exists in LibreOffice 3.5.4.2
Comment 7 Rainer Bielefeld Retired 2012-08-14 17:41:49 UTC
At least I can confirm the Locale related difference in various LibO Versions like with parallel installation of Master "LOdev  3.7.0.0.alpha0+   - WIN7 Home Premium (64bit) ENGLISH UI [Build ID: 7d8cd0a]" (tinderbox: 2008R2@20, pull time 2012-08-10 23:27:17)

But I can't tell whether the problem really is a bug.

I see in an example similar to reporter's:
07/04/97
15
15
(that's OK)

07/04/98
14
15
(that's NOT OK, the problem appears 1998)

With German locale I see for reporter's sample:
07.02.11
6
6

but 
04.07.98
26
27

Currently I can't tell whether that really is a bug. May be =Weeknum() uses an other week counting scheme that a localized formatting WW?

@Eike:
Can you bring some light into the dark?

@seumas:
Why do you think this is a bug? Can you cite some Help, norms, ...? Only "unexpected" does not make a bug.
Comment 8 seumas 2012-08-15 10:00:03 UTC
Well, I would have have thought that asking for the week number and getting two different answers, depending on where you asked, would obviously be a bug! :)

weeknum() is the one returning the correct value. The WW formatting seems to have different code (why?! - surely it should basically be applying a formula to itself?) and gives what appears to the the USA calculation for week number.

Under the Europe/ISO standard, the first week of the year is the week that has the first Thursday of the year.

Under the US standard, the first week of the year is the week that has the first of January (or something; I don't know the exact calculation).

That's why some years they match up and some years they don't. But it's not like it requires any new code: weeknum() gets it right! Just make sure that WW is using the correct weeknum() code, yes?
Comment 9 Rainer Bielefeld Retired 2012-08-15 11:58:35 UTC
@seumas:
Sounds plausible

@Eike, András:
I did a quick test in a WRITER table, also delivers Week 7 inserting "07/02/11" with 'Locale settin = English UK' (others not tested). That problem seems to related to reporter's.
Comment 10 QA Administrators 2015-01-05 17:52:27 UTC Comment hidden (obsolete)
Comment 11 seumas 2015-01-06 12:53:16 UTC
Bug still exists exactly as previously described.
Comment 12 QA Administrators 2016-01-17 20:05:16 UTC Comment hidden (obsolete)
Comment 13 QA Administrators 2017-03-06 14:38:34 UTC Comment hidden (obsolete)
Comment 14 Eike Rathke 2017-03-06 15:25:25 UTC
The number formatting week number is locale dependent, the spreadsheet function WEEKNUM and related are not.
Comment 15 seumas 2017-03-07 11:24:23 UTC
Actually, it was the other way around: weeknum() was giving the correct locale dependent week. The WW formatting was giving the incorrect week.

I just tested with Version: 5.1.6.2. The good news is that both methods return the same week number. The bad news is that they are now *both* incorrect. Both return Week 7 for 07/02/2011 (7th February)
Comment 16 V Stuart Foote 2017-03-13 14:13:33 UTC
Created attachment 131854 [details]
sample document with WEEKNUM in mode 21 (ISO 8601) -- adjust Locale to test

(In reply to seumas from comment #15)
> Actually, it was the other way around: weeknum() was giving the correct
> locale dependent week. The WW formatting was giving the incorrect week.
> 
> I just tested with Version: 5.1.6.2. The good news is that both methods
> return the same week number. The bad news is that they are now *both*
> incorrect. Both return Week 7 for 07/02/2011 (7th February)

No Eike is correct, the WEEKNUM is hardcoded to the selected MODE. Locale has no impact on the calculation.

https://help.libreoffice.org/Calc/WEEKNUM

But for English (UK) locale WW date formatting is not handled as a ISO 8601, French (France) and German (Germany) locales both are.  And, English/French (Canada) has me scratching my head.

In the attached sample document I've set the WEEKNUM to mode 21 to force ISO 8601, simply change the Tools -> Options -> Languages Settings -> Languages: Local setting to locale.

If the locale based English (UK) WW should be ISO 8601 it is not--and this remains valid.
Comment 17 QA Administrators 2019-01-18 03:59:05 UTC
** Please read this message in its entirety before responding **

To make sure we're focusing on the bugs that affect our users today, LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed bugs which have not been touched for over a year.

There have been thousands of bug fixes and commits since anyone checked on this bug report. During that time, it's possible that the bug has been fixed, or the details of the problem have changed. We'd really appreciate your help in getting confirmation that the bug is still present.

If you have time, please do the following:

Test to see if the bug is still present with the latest version of LibreOffice from https://www.libreoffice.org/download/

If the bug is present, please leave a comment that includes the information from Help - About LibreOffice.
 
If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a comment that includes the information from Help - About LibreOffice.

Please DO NOT

Update the version field
Reply via email (please reply directly on the bug tracker)
Set the bug's Status field to RESOLVED - FIXED (this status has a particular meaning that is not 
appropriate in this case)


If you want to do more to help you can test to see if your issue is a REGRESSION. To do so:
1. Download and install oldest version of LibreOffice (usually 3.3 unless your bug pertains to a feature added after 3.3) from http://downloadarchive.documentfoundation.org/libreoffice/old/

2. Test your bug
3. Leave a comment with your results.
4a. If the bug was present with 3.3 - set version to 'inherited from OOo';
4b. If the bug was not present in 3.3 - add 'regression' to keyword


Feel free to come ask questions or to say hello in our QA chat: https://kiwiirc.com/nextclient/irc.freenode.net/#libreoffice-qa

Thank you for helping us make LibreOffice even better for everyone!

Warm Regards,
QA Team

MassPing-UntouchedBug