Ask Your Question

Conditional formatting based on week day [closed]

asked 2015-12-26 12:03:11 +0100

lovecraft22 gravatar image

updated 2020-08-09 20:28:36 +0100

Alex Kemp gravatar image

Dear all, is there any way to apply a conditional formatting based on the week day filled in the cell? I have cells with the following format: Sun 31/12/2015 I would like Sundays to have a different background color.

Working with CALC on a Mac


edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-08-09 20:28:55.509722

2 Answers

Sort by » oldest newest most voted

answered 2015-12-31 00:00:24 +0100

doug gravatar image

updated 2016-01-04 15:42:23 +0100

Let's say the date column is A. In a separate column, run the formula =WEEKDAY(A1; 2). The documentation for the function is here. Monday will be 1, Friday 5, Sunday 7.

Then, apply Conditional Formatting on the new column, every value less than or equal to 5, have that be your weekday color, add another style and condition for greater than 5, that will be your weekend color. Conditional formatting documentation here.

As explained by @Lupp, these steps can be consolidated by using a conditional formatting Formula from the dropdown list. The following steps will succeed in reproducing @Lupp's finished product:

  1. Select the first cell in the list of dates (A1);
  2. Add Conditional Formatting;
  3. Select from the dropdown list in the formatting dialog box, Formula;
  4. Next to the Formula dropdown selector, enter the formula: WEEKDAY(A1; 2) <=5 (assuming you are in cell A1);
  5. Pick/edit the style with the background to suit;
  6. Accept conditional formatting, close dialog;
  7. Copy contents of cell in sheet view using Ctrl-c;
  8. Select rest of column of dates;
  9. Use Paste Special function (right mouse click or Edit on menu);
  10. In Paste Special dialog, pick Format only for paste, unselect all others, press ok.

Now the dates are formatted the way you desire.

edit flag offensive delete link more


Perfect, thanks!

lovecraft22 gravatar imagelovecraft22 ( 2016-01-01 10:46:06 +0100 )edit

If the answer solves your question please tick the ✔.

m.a.riosv gravatar imagem.a.riosv ( 2016-01-05 22:13:59 +0100 )edit

Excellent solution and a good search functionality! Big thanks!

ROSt52 gravatar imageROSt52 ( 2020-10-16 11:03:45 +0100 )edit

answered 2015-12-31 01:40:29 +0100

Lupp gravatar image

CF conditions can be arbitrary formulae returning a logical (Boolean) result. Relative addressing is to give with respect to the topmost leftmost cell of the range.

See also attached. ask62328CFbyWeekday001.ods

edit flag offensive delete link more

Question Tools

1 follower


Asked: 2015-12-26 12:03:11 +0100

Seen: 3,583 times

Last updated: Aug 09 '20