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