Calc - Conditional formatting - start of the week -> why not Monday?

Hrbrgr · April 3, 2023, 9:35am

When i creating a Conditional Formatting in Calc for date (condition. next week), then Sunday is defined as the start of the week.

In the operating system (W10) Monday is defined as the start of the week.

Am I still missing a setting for the start of the week, Sunday or Monday or is this a bug.

90040 HB Kap 04 Testdatei Datum.ods (16,7 KB)

Version: 7.5.2.2 (X86_64) / LibreOffice Community
Build ID: 53bb9681a964705cf672590721dbc85eb4d0c3a2
CPU threads: 8; OS: Windows 10.0 Build 19045; UI render: Skia/Raster; VCL: win
Locale: de-DE (de_DE); UI: de-DE
Calc: CL threaded

PKG · April 3, 2023, 10:37am

Excel behaves the same way.

Aktuelle Kalenderwoche

EarnestAl · April 3, 2023, 10:41am

My printed calendars all start on Sunday but ISO week date - Wikipedia says “Weeks start with Monday and end on Sunday.” Biblically speaking, I guess that’s right but because of compatibility with Excel…

Bug 119407 - Allow flexible start-of-week days in Previous/This/Next Week conditional formattings

karolus · April 3, 2023, 11:02am

simple workaround:
sorry the formula should read:

INT((B1-2)/7)-1 = INT(NOW()/7)

Hrbrgr · April 3, 2023, 11:52am

@PKG ; @EarnestAl ; @karolus

First of all, thank you very much for your contributions.

Excel solution
Bug
Workaround

Since I need it for documentation, I will point out the bug.

sokol92 · April 3, 2023, 2:16pm

What will happen to the formula on 2023-04-08?

karolus · April 3, 2023, 3:06pm

good catch! … each bracket counts, the correct formula is
INT((B1-2)/7)-1 = INT(NOW()/7)

sokol92 · April 3, 2023, 3:24pm

I think the correct formula is

=INT((B1-2)/7)=INT((TODAY()+5)/7)

with the same idea (provided that in the Calc parameters, the 0th day corresponds to 1899-12-30).

mikekaganski · April 3, 2023, 5:43pm

formally, given that the epoch can have three different starts in Calc, each on own weekday, the correct and compatible (i.e., working in Excel, even in 1904-01-01-base files), but slower, formula would be

INT((B1-DATEVALUE("1904-01-04"))/7)=INT((TODAY()+7-DATEVALUE("1904-01-04"))/7)

relying on the fact that 1904-01-04 is a (~randomly chosen) Monday no earlier than the latest possible epoch.

mikekaganski · April 3, 2023, 6:03pm

OTOH, the WEEKS function with Type parameter 1 is created specifically for this:

WEEKS(TODAY();B1;1)=1