Bug 54346 - Libreoffice 3.5.5 Calc WEEKDAY() function shows wrong number of day
Summary: Libreoffice 3.5.5 Calc WEEKDAY() function shows wrong number of day
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
3.5.5.3 release
Hardware: Other All
: medium normal
Assignee: Winfried Donkers
URL:
Whiteboard: target:5.1.0 target:5.0.0.0.beta2
Keywords:
Depends on:
Blocks: Conditional-Formatting
  Show dependency treegraph
 
Reported: 2012-09-01 09:53 UTC by andis.lazdins
Modified: 2017-07-17 10:40 UTC (History)
7 users (show)

See Also:
Crash report or crash signature:


Attachments
Sample file (18.35 KB, application/vnd.oasis.opendocument.spreadsheet)
2012-09-01 09:53 UTC, andis.lazdins
Details
description cause of the misunderstanding (52.91 KB, image/png)
2015-05-21 16:36 UTC, c.burais
Details

Note You need to log in before you can comment on or make changes to this bug.
Description andis.lazdins 2012-09-01 09:53:24 UTC
Created attachment 66437 [details]
Sample file

Libreoffice 3.5.5 Calc WEEKDAY() function applies wrong number of day in Conditional formatting applications. Sample file follows in attachment.
When I try to set certain formatting to Sundays and Saturdays, like in column A in sample file (formula to apply different style is OR(WEEKDAY(A1)=6;WEEKDAY(A1)=7)). If default Date format (12/30/1899) is selected, special formatting is applied to Fridays and Saturdays, if Date format 01/01/1904 is selected, special format is applied to Saturdays and Sundays like predicted, if Date format 01/01/1900 (StarCalc 1.0) is selected, special formatting is applied to Wednesdays and Trursdays. Value of WEEKDAY() in column C at the same time is not changing with no respect to selected Date format.

I don't know when this problem appeared, but I used this application for several years without any problems since I found it by accident.
Comment 1 andis.lazdins 2012-09-01 09:54:35 UTC
System information Ubuntu 12.04 with Xfce. Libreoffice 3.5.5
Comment 2 GerardF 2012-09-01 13:01:04 UTC
I think there is no problem with conditional formatting here.

OR(WEEKDAY(A1)=6;WEEKDAY(A1)=7) returns TRUE for Friday and Saturday with type=1 (or missing) in WEEKDAY(date;type).

Changing date origine in options modifies formatting accordingly .
But columns B (TEXT function) and C (WEEKDAY function) need hard calculation (Ctrl+Shift+F9) after changing option.

I don't know if we should consider this as a bug ?
Comment 3 andis.lazdins 2012-09-01 13:13:59 UTC
> 
> I don't know if we should consider this as a bug ?

Of course, it is developers' decision; however, I'm sure that formulas should return the same result at any type of usage - in cells entered directly or in conditional formatting formula box. For me it took some 30 minutes to find solution, for computer experts it will take may be 10 minutes, newbie will decide that this function is not working any more and will drop it considering that Libreoffice is loosing functionality.
Comment 4 Winfried Donkers 2013-03-01 12:26:43 UTC
This looks like a non-bug to me.
The WEEKDAY() function returns the value as designed and as explained in th ehelp file, with the default setting (no type specified) being Sunday day 1.

Changing the type in either column A, B or C changes the result instantly (provided that auto-calculation is enabled).

Tested with version 4.0.0.3 on Windows XP, version 3.6.5 on Windows 7-64.

@andis:
If you feel this is till a bug, please put the status back to unconfirmed and tell what is the bug. Thank you :)
Comment 5 Eugen Simon 2014-01-30 20:06:56 UTC
I've been using LibreOffice for a while and recently I have tried to use the WEEKDAY () function, but I discovered that no matter how much I try to change settings all the time counting the wrong days. The transition between two months never match next month's first date. Not only that. If I write 2014 or 14 will never Return with same numbers, but it should do, at least according to what help or documentation writing. Then I looked the other date function also they make errors too. WEEKDAY () function makes another error if I Uses the normal function, or differently if I want to use it as conditional formatting.

With friendly greetings Eugen Simon 

P.S. I'm using Windows 8.1 pro and LibreOffice 4.1.4.2
Also tested with 4.2 but even with this result is the same.
Comment 6 Winfried Donkers 2014-01-31 07:06:34 UTC
(In reply to comment #5)

@Eugen Simon: 
Your problem is not clear to me (I can't test the information you provide and so can't investigate your problem).
Can you upload a sample document with a step by step description of what you do, what you except and what you get instead?
Also, if you think your problem is different or larger than the problem described in this (closed) bug report, it would be better to create a new bug report (please add me as cc in that case). Adding comments to a closed report will not alert QA people, only those that are on the cc list of this bug.
Comment 7 andis.lazdins 2014-01-31 09:57:04 UTC
Hello!

The problem with weekday() function is still there for me. I just stopped to use this function after conclusion on this bug report that this is not a bug, but feature :) Probably this problem appears only in case, if it is set, that week starts with Monday or additional operators should be used in formula.

Considering that developers are not planning to deal with this and there is very small interest on this issue, I recommend to find a workaround (there are several options, like transforming weekdays into text and setting number of day according to text strings) and not to use weekday() function in calculations.
Comment 8 Winfried Donkers 2014-01-31 11:24:28 UTC
(In reply to comment #7)

Hi,

if you want the week to start on a different day than Sunday, you need to use the 2nd argument in function WEEKDAY.
This argument can have 3 values:
1 (same as no 2nd argument), week starts on Sunday (days from 1 - 7)
2, week starts on Monday (days from 1 - 7)
3, week starts on Monday (days from 0 - 6)
Note: the first argument (the date) must have the same base as set in Extra-Options-LibreOffice Calc-Calculate.

AFAICS, the above works as intended.
I will look into this again when different behaviour is reported (with step by step instructions of how to reproduce the problem).
Comment 9 c.burais 2015-05-21 16:36:52 UTC
Created attachment 115777 [details]
description cause of the misunderstanding
Comment 10 c.burais 2015-05-21 16:40:58 UTC
Comment on attachment 115777 [details]
description cause of the misunderstanding

The misunderstanding is coming from the faulty description of the function said that the number of the day is 1-7 that is not true it can be 0.
and the description of the second argument doesn't gives the information about the different values...
hope it can help
Comment 11 Winfried Donkers 2015-05-22 07:23:04 UTC
(In reply to c.burais from comment #10)
> Comment on attachment 115777 [details]
> description cause of the misunderstanding
> 
> The misunderstanding is coming from the faulty description of the function
> said that the number of the day is 1-7 that is not true it can be 0.
> and the description of the second argument doesn't gives the information
> about the different values...
> hope it can help

I see what you mean.
The description in the function wizard mentions 1-7 as range, and the help text explains that the range is dependent on the value of argument Mode (1 and 2 have output range 1-7 and Mode 3 has output range 0-6).

I will remove the confusing 1-7 in the function wizard.
Comment 12 Commit Notification 2015-05-22 17:33:21 UTC
Winfried Donkers committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=b2cf675b827912feb5edfbca1ea902d1ab92ed6b

tdf#54346 remove confusing description for function WEEKDAY

It will be available in 5.0.0.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.
Comment 13 Commit Notification 2015-05-22 18:31:54 UTC
Winfried Donkers committed a patch related to this issue.
It has been pushed to "libreoffice-5-0":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=4749cac606fc1ab47c0e50322de46c63e1c2a5bc&h=libreoffice-5-0

tdf#54346 remove confusing description for function WEEKDAY

It will be available in 5.0.0.0.beta2.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.
Comment 14 andis.lazdins 2017-07-17 10:40:27 UTC
The solution works fine in 5.2.7.
Thank you a lot!