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: CLOSED DUPLICATE of bug 139421
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 tree / graph
 
Reported: 2011-02-01 03:31 UTC by seumas
Modified: 2021-01-18 14:57 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
View All Add an attachment (proposed patch, testcase, etc.)

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 Comment hidden (obsolete)
Comment 18 QA Administrators 2021-01-18 04:08:12 UTC Comment hidden (obsolete)
Comment 19 Eike Rathke 2021-01-18 14:57:09 UTC 
Better details in the other bug.

*** This bug has been marked as a duplicate of bug 139421 ***