37

How can I conditionally format cells in Google spreadsheets containing dates to have a different background according to the day of the week.

For example, if a date falls on Saturday or Sunday, set background color to yellow, else white.

5 Answers 5

31

I had been struggling with this for a long time, but finally cracked it:

Use conditional formatting on the column with the dates and type the following as a custom formula:

=or(WEEKDAY(A1)=1,WEEKDAY(A1)=7)

where A1 is the first date in the column.

This will apply the conditional formatting to all weekdays with a value of 1 (Sunday) and 7 (Saturday).

2
  • 3
    This is a good solution, thanks. I'm accepting it. For my use I've adapted it to exclude blank cells: =and(not(isblank(A1)), or(WEEKDAY(A1)=1,WEEKDAY(A1)=7))
    – Mike Ellis
    Feb 7, 2018 at 15:55
  • Nice addition with the not isblank. I had been using it in a manner where I only used it for the range containing the dates, e.g. A1:A31. I also changed it from using > and < to = like you did. It's a little more straight forward that way. I guess I started out hoping for weekday numbering more like it is in my country where Monday would be 1 and Sunday would be 7. I don't know if this is the case if the country setting is changed.
    – aej
    Feb 8, 2018 at 3:31
17

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:

=weekday(A1)=1  

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.

1
  • 6
    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 Dec 3, 2015 at 14:24
6

If the dates are in column A, select them, then do: Format > Conditional formatting... > Format cells if... > Custom formula and put

=and(isblank(A:A)=false, or(weekday(A:A)=1, weekday(A:A)=7))

Some explanation:

weekday(A:A) returns 1-7, for day of the week, and or(weekday(A:A)=1, weekday(A:A)=7) returns true, if it's Sunday (1) or Saturday (7).

This would work on it's own, but for some reason weekday() on an empty cell returns 7, so isblank(A:A)=false also checks if the cell is empty. Now it colors a cell if it is both (not empty) and (sun or sat)

I also put A:A, as A1 would give offset results if the format range didn't start at the top (e.g. A5:A100 instead of A1:A100)

Credits to pnuts!

4

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) 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.

2
  • 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, 2013 at 21:49
  • I changed the test in the if statement to be more straightforward. Does it work now? Apr 29, 2013 at 13:57
2

To conditionally format cell A6, in which we have a date, I used the option under conditional format *Custom formula is* (bottom drop down) and put in the formula

=weekday(A6,2)<6

This is for condition on cell A6

If you want to mark the Saturdays & Sundays, use:

=weekday(A6,2)>5

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