Ask Your Question

Specify first day of Week for all Documents

asked 2017-04-01 14:44:13 +0100

Klamann gravatar image

In a calc spreadsheet, I have rows with consecutive dates per row. Now I'd like to highlight the days of the current week, so I've selected conditional formatting and chose a different template for the condition "date" is "this week" (not sure about the exact terms here, I'm using the german locale).

Unfortunately, this selects the sunday of the last week up to the saturday of the current week, e.g. currently it selects March 26th to April 1st.

According to ISO 8601, Monday is the first day of the week, and this is the behaviour I would expect. Is there a way to tell LibreOffice that Monday is the first day of the week or is this a bug?

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted

answered 2017-04-01 15:20:06 +0100

Lupp gravatar image

updated 2017-04-01 15:23:41 +0100

I do not know a way to set a property as you are looking for. But the respective functions WEEKNUM and WEEKDAY have an optional second parameter specifying the day to be taken as the first day of the week. Placing the number 2 there will choose the monday.
Even better: For some time now there is an international ISO standard specifying the monday as the first day of the week (and also how to number the weeks of a year). Thus you mayse the function ISOWEEKNUM(Date) without a second parameter to check any date for lying in the current monday-based week by (e.g.) =ISOWEEKNUM(*Date*) = ISOWEEKNUM(TODAY()). If your range of dates is spanning over multiple years you may combine the above formula with something like
ABS(Date-TODAY())<=6 or similar using the AND function. There are many variants and you may play a bit to find the one looking prettiest to you. See also this attachment.

I am German and living in Germany as you seem to, but I always use an English User Interface (UI) to be prepared for international cooperation. The locale I set as needed on the fly.
If you have a question very specific to the German environment, there is this German branch of the LibO askbot site.

edit flag offensive delete link more


thanks for your reply, it worked for me. Although I find it quite cumbersome to input this formula into the conditional formatting dialogue, especially since there is already a predefined function which should solve the problem (but does not do so correctly, according to ISO and/or my selected locale).

Klamann gravatar imageKlamann ( 2017-04-01 15:34:15 +0100 )edit

What is considered "correctly" concerning the first day of a week is depending not only on regional or "cultural" preferences, but even on personal ones.
A very personal opinion:
I prefer to be independent of features easily screwed up and probably subject to changes without notice. I also will never use other tricky options of CF.
I am not interested in software consisting of 90% gimmicks. I want software delivering reliable results and can dispense with gay toys.

Lupp gravatar imageLupp ( 2017-04-02 00:06:35 +0100 )edit

answered 2017-04-01 15:38:19 +0100

karolus gravatar image

updated 2017-04-01 15:42:50 +0100


Select such row, for Example: A3:Z3 and change the Condition to:

Formula is … WEEKNUM(A3;2)=WEEKNUM(TODAY();2)
edit: german Formula-names: Formel ist … KALENDERWOCHE(A3;2)=KALENDERWOCHE(HEUTE();2)

I agree, its a bug that the particular Condition »Date« is »this Week« doesn't match your locale rules for first and last days of week

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2017-04-01 14:44:13 +0100

Seen: 250 times

Last updated: Apr 01 '17