Download it now!
Bug 112320 - FILEOPEN: WEEKNUM in an XLSX gives different result than in Excel
Summary: FILEOPEN: WEEKNUM in an XLSX gives different result than in Excel
Status: RESOLVED NOTOURBUG
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
5.1.0.3 release
Hardware: All All
: medium normal
Assignee: Winfried Donkers (retired)
URL:
Whiteboard:
Keywords: bibisected, bisected, filter:xlsx, regression
Depends on:
Blocks:
 
Reported: 2017-09-11 05:41 UTC by Mike Kaganski
Modified: 2017-09-26 11:13 UTC (History)
6 users (show)

See Also:
Crash report or crash signature:


Attachments
WEEKNUM in Excel reference sheets (2.49 MB, application/zip)
2017-09-13 19:40 UTC, Mike Kaganski
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Mike Kaganski 2017-09-11 05:41:09 UTC
When opening attachment 113150 [details] from bug 89139, the WEEK column on Dati sheet shows 1 for cells D727:D730, while in Excel, these give 53. If the function WEEKNUM in these cells is replaced with WEEKNUM_EXCEL2003, then the result is as expected.
Comment 1 Buovjaga 2017-09-11 07:15:49 UTC
Confirmed.

Arch Linux 64-bit, KDE Plasma 5
Version: 6.0.0.0.alpha0+
Build ID: 09122a537318f7ada075820f3b1ef83a64e56751
CPU threads: 8; OS: Linux 4.12; UI render: default; VCL: kde4; 
Locale: fi-FI (fi_FI.UTF-8); Calc: group
Built on September 10th 2017
Comment 2 Xavier Van Wijmeersch 2017-09-11 07:35:40 UTC
confirm with

Version: 5.4.2.0.0+
Build ID: e510fbc21f6dec877cda04e17f1433f09fa00066
CPU threads: 8; OS: Linux 4.9; UI render: default; VCL: kde4; 
Locale: nl-BE (en_US.UTF-8); Calc: group

OS slackware64 current
Comment 3 MM 2017-09-11 21:34:14 UTC
Unconfirmed with V5.0.6.3 under Mint 17.3 x64.
Confirmed with V5.1.6.2 under Ubuntu 16.04 x64.
Comment 4 Aron Budea 2017-09-12 20:14:24 UTC
Bibisected to the following commit using repo bibisect-win32-5.1. Adding Cc: to Winfried Donkers.

https://cgit.freedesktop.org/libreoffice/core/commit/?id=f830600ece806ec365a4839e79afabe183c5e36d
author		Winfried Donkers <winfrieddonkers@libreoffice.org>	2015-09-22 08:40:25 (GMT)
committer	Eike Rathke <erack@redhat.com>	2015-10-06 21:21:16 (GMT)

"tdf#50950 Add ODFF1.2 Calc function ISOWEEKNUM,"
Comment 5 Winfried Donkers (retired) 2017-09-13 05:53:33 UTC
(In reply to Mike Kaganski from comment #0)
> When opening attachment 113150 [details] from bug 89139, the WEEK column on
> Dati sheet shows 1 for cells D727:D730, while in Excel, these give 53. If
> the function WEEKNUM in these cells is replaced with WEEKNUM_EXCEL2003, then
> the result is as expected.

@Mike: Can you tell me which version of Excel you used?
Comment 6 Mike Kaganski 2017-09-13 06:05:57 UTC
(In reply to Winfried Donkers from comment #5)
> @Mike: Can you tell me which version of Excel you used?

Oh sorry - forgot to mention: I used Excel 2016.
Now I re-tested with Excel 2007 and on Excel 2003 (with Compatibility Pack) with the same result (53 in those cells).
Comment 7 Mike Kaganski 2017-09-13 06:11:33 UTC
Ignore the Excel 2003 part - it appears that there it was only a cached value; recalculating there gives error (#NAME). 2007 and 2016 work as I described though (just made both recalculate).
Comment 8 Winfried Donkers (retired) 2017-09-13 07:55:39 UTC
(In reply to Mike Kaganski from comment #7)
> Ignore the Excel 2003 part - it appears that there it was only a cached
> value; recalculating there gives error (#NAME). 2007 and 2016 work as I
> described though (just made both recalculate).

OK, thanks, I'll see what i can do to fix it.
Comment 9 Winfried Donkers (retired) 2017-09-13 17:07:47 UTC
It looks as if Excel is to blame, please correct me when I am wrong:

second argument of WEEKNUM 1(or missing),2,11-17: 
the week with 1 January is week 1; the week begins on Sunday, Monday, Monday-Sunday. Both ODFF1.2 and Excel seem to use the same definition.

So, for Monday 29 Dec 2014 (1 Jan 2015 is Thursday) one would expect 26 December 2014 to be in the same week as 1 January 2015, i.e. week 1. However, Excel returns week 53.

It is clear when you look at a longer period in Excel:
21..27 December 2014 are in week 52, 7 days, ok.
28..31 December 2014 are in week 53, 4 days, that cannot be right.
1..3 January 2015 are in week 1, 3 days, that cannot be right.
4..10 January 2015 are in week 2, 7 days, ok.

Unless I discover where I make a mistake (or someone points me to my mistake), I intend to set this bug report to RESOLVED - NOTOURBUG.
Comment 10 Winfried Donkers (retired) 2017-09-13 17:13:51 UTC
(In reply to Winfried Donkers from comment #9)
> It looks as if Excel is to blame, please correct me when I am wrong:
[...] 
> Unless I discover where I make a mistake (or someone points me to my
> mistake), I intend to set this bug report to RESOLVED - NOTOURBUG.

@Eike: can you tell me if/where I'm wrong?
Comment 12 Mike Kaganski 2017-09-13 19:40:21 UTC
Created attachment 136232 [details]
WEEKNUM in Excel reference sheets

In this attachment, the first sheet shows all results for WEEKNUMs for last day of year and first day of next year for each result type. The second sheet concentrates on result type 21, and shows how the week number depends on number of days left in year (or days of this week that are in next year).
Comment 13 Winfried Donkers (retired) 2017-09-14 05:44:52 UTC
(In reply to Mike Kaganski from comment #11)
> https://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel-
> mso_winother-mso_archive/weeknum-function-in-excel-2003-or-2007/08dd5e58-
> 3e8f-4bd0-8d41-f7bfe8cf2d6d

Thanks, very helpful!

Extract from your link:
"In the US week numbering system, Week 1 begins on January 1. All other weeks begin on Sunday. So Week 1 begins on Thursday, January 1, 2015, and it has only 3 days, since Sunday, January 4 is the beginning of the next week, i.e. Week 2. December 28, 2014 through December 31, 2014 form week 53 of 2014 - a week with only 4 days."
This makes clear what happens in Excel. WEEKNUM without second argument default to type 1, I now limit myself to type 1, as there is no problem with type 21.

Now I need to sure that I interpret http://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part2.html#__RefHeading__1018212_715980110 correctly:
"For Mode={1, 2, 11, 12, ..., 17} the week containing January 1 is the first week of the year, and is numbered week 1. The week starts on {Sunday, Monday, Monday, Tuesday, ..., Sunday}.".
If this is to be interpreted like Excel operates, then a) I must modify the WEEKNUM code and b) I think ODFF1.2 must be formulated unambiguous.

@Eike: can you shed light on how ODFF1.2 is to be interpreted?
Comment 14 Winfried Donkers (retired) 2017-09-25 07:10:48 UTC
So far, I have found no consistent definition of the US-week numbering. Microsoft seems to be consistent in stating that the first/last week of the year is a partial week (i.e. 7 days or less).
Other sources (too much to mention)I found indicate that there are _no_ partial weeks and e.g. 2014-12-29 is in week 1.
MariaDB SQL command WEEK('2014-12-29') returns 1 (with default_week_format 0, meaning 1st day of week is Sunday, week 1 is 1st week with Jan 1).
I have found no standards that define the US week numbering system.
Islamitic week numbering systems have Saturday as the first day of the week and  week 1 is 1st week with Jan 1 (mode 16). Excel returns week 53, which is not correct.

The code used to determine the week number in Calc is used in various other places in LibreOffice as well. I see no reason to change such code.

The reason that WEEKNUM_EXCEL2003 returns the same numbers as Excel does, is that this function is the old Excel Add-In function, with all it's peculiarities.

My conclusion is that Microsoft uses it's own definition of week numbering and that WEEKNUM complies with ODFF1.2 and the generally used definition of week numbers.

Calc offers a function that is Excel-compatible (WEEKNUM_EXCEL2003).
It can be said just as well that WEEKNUM saved by Excel as/read by Excel from an ODF document returns the wrong value.
Comment 15 Mike Kaganski 2017-09-25 07:13:38 UTC
I didn't imply that our WEEKNUM must change; but isn't it the proper solution to use WEEKNUM_EXCEL2003 to interchange with Excel?
Comment 16 Winfried Donkers (retired) 2017-09-25 07:41:52 UTC
(In reply to Mike Kaganski from comment #15)
> I didn't imply that our WEEKNUM must change; but isn't it the proper
> solution to use WEEKNUM_EXCEL2003 to interchange with Excel?

That is currently still a problem. The Add-In functions, of which WEEKNUM_EXCEL2003 is one, are handled completely separately and I am still working (progress extremely slow) on a solution for that (see bug #106013).

It is possible to switch from WEEKNUM to WEEKNUM_EXCEL2003 when opening an OOXML document, but that does not solve the problem:
-it only works one way;
-Calc documents saved as OOXML and then reopened by Calc produce changes in results.
-For other modes that 1 and 17 (US week numbering) the Excel result are IMHO really incorrect.
Comment 17 Luke 2017-09-25 15:52:00 UTC
Winfried Donkers,
How about using the compatibility mode, so that we interpret it differently in Excel documents?
Comment 18 Eike Rathke 2017-09-26 09:12:05 UTC
(In reply to Luke from comment #17)
> How about using the compatibility mode, so that we interpret it differently
> in Excel documents?
Doesn't work as soon as it was saved in another file format. Basing calculation of the same function on file formats is a dead end.
Comment 19 Eike Rathke 2017-09-26 09:45:16 UTC
For the amusement of the reader:
https://www.mrexcel.com/forum/excel-questions/367162-weeknum-returns-53-weeks-post1819547.html#post1819547              
                                                                                                                        
| The way WEEKNUM function works week 1 can be as short as 1 day (when 1st                                              
| Jan is a Saturday) and when that co-incides with a leap year (averaging                                               
| about once every 28 years) you also get a week 54, for one day only, on                                               
| 31st December, e.g.                                                                                                   
|                                                                                                                       
| =WEEKNUM("2028-12-31")=54                                                                                             

...