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?
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.
Repeat for the other required formats, adjusting the
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.
This is possible with a script. Go to Tools → Script editor... and paste in this script:
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.