Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

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

1 Answer 1

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)) {
    cell.setBackground("red");
  } else {
    cell.setBackground("white");
  }
}

Save the script (give the project a name) ad 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

 
discard

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.