How do I mark weekends red in LibreOffice Calc?

Question

I have a list with dates in it, and I would like to mark the dates representing weekends as red (for easier telling which day of the week it is without having to manually look up the day of the week at the left, and just for extra emphasis). There's no option in the Format Cells dialog to do this, though (although there is an option to mark negative numbers red in the Number format). How could I go about doing this manually?

Solution 1

Use conditional formatting with the following formula WEEKDAY(A2;2)>5

You can use this format in all cells Monday-Sunday and only the weekends will be highlighted. In this example I highlight the cell ABOVE the actual date. You can format muliple cells in a column by "fixing" the cell from which you get the date: WEEKDAY(A$2;2)>5 (WEEKDAY(A$2,2)>5). As you can see, I use the same for the current date (in cell DX2) where the current date is coloured blue.

Important 1:

I have experienced a lot of conditional format problems in LibreOffice, especially that the conditional formats don't "stick" and 80% is lost after you saved and then re-opened the file. Switching over to OpenOffice completely resolved that issue for me!

Important 2:

Depending on the country settings, formulae either use ; or , as a separator. So the formula in cell A1 could also be: WEEKDAY(A$2,2)>5

Solution 2

1. Select a cell (C1 in this example)
2. Select the range you want (I clicked on the C header)
3. Click menu Format > Conditional Formatting > Condition...
4. Change the dropdown to "Formula is"
5. Enter WEEKDAY(C1,2)>5 into the text box next to the dropdown.
   1. C1 is the currently selected cell in the range (the one outlined in bold)
   2. 2 means "Start week from Monday"
   3. >5 means after the fifth day of the week, i.e. days after Friday
   4. Weekday function reference
6. Create/select a style. I've created a new one here using "New Style..." in the dropdown
7. Click "OK"

Curiously you can only get back to editing this format if you click the same cell again before picking the range.

Comments

[Tim Abell]

see also ask.libreoffice.org/en/question/19950/…

[Joe Z.]

No, I'm talking about having the date be automatically red if it's a weekend.