Web Applications Stack Exchange is a question and answer site for power users of web applications. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

How can I conditionally format cells in Google spreadsheets containing dates to have a different background according to the day of the week, e.g highlight the Saturdays and Sundays in a column containing the days of the month?

share|improve this question

Easy (but a bit tedious!) in new Google Sheets. For Conditional Formatting a new rule is required for each colour, roughly. (One colour can usually be skipped from rules and instead applied with normal formatting - which would be overidden where any of the conditions for CF apply.) So expect to repeat the basics of the following at least five times.

Say the column of dates is A. Select it, Format > Conditional formatting..., Custom formula is and enter:


Choose the formatting required.

The final 1 above is for Sunday, other days follow in numeric order.

Repeat for the other required formats, adjusting the 1 as necessary.

Because these rules do not conflict (each date is only one specific day of the week) the order in which the rules are added (with "+ Add another rule") does not matter.

share|improve this answer
Building on this, a simpler way would be to do =WEEKDAY(A1,2)>5 - This will hit both Saturday and Sunday and only require one rule – FuriousGeorge Dec 3 '15 at 14:24
@FuriousGeorge I think just a different interpretation of the requirement. Yes the body has highlight the Saturdays and Sundays but that is prefixed e.g and the Title has by day of the week. – pnuts Dec 3 '15 at 14:29

This is possible with a script. Go to ToolsScript editor... and paste in this script:

function onEdit(e) {
  var cell = e.range.getCell(1, 1);
  var val = cell.getValue();
  if ((val instanceof Date) && (val.getDay() == 0 || val.getDay() == 6)) {
  } else {

Save the script (give the project a name) and go back to your spreadsheet.

Now, every time you edit a cell, this script will check to see if you entered a date and if the date you entered is a Sunday or Saturday. If so, the background of the cell will change to red. If it is not a date or it is not on the weekend, the background will change to white.

There are other color names you can use, or you can use a hex code to specify a different color.

share|improve this answer
Thanks, William. Although the code looks sensible, for some reason, it doesn't quite work as expected. It turns even-numbered days red and odd-numbered days white. – Mike Ellis Apr 26 '13 at 21:49
I changed the test in the if statement to be more straightforward. Does it work now? – William Jackson Apr 29 '13 at 13:57

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.