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.
System information Ubuntu 12.04 with Xfce. Libreoffice 3.5.5
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 ?
> > 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.
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 :)
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.
(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.
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.
(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).
Created attachment 115777 [details] description cause of the misunderstanding
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
(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.
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.
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.
The solution works fine in 5.2.7. Thank you a lot!