How to Highlight Weekends in Google Sheets

When working with many data with different weekdays in Google Sheets, sometimes you may need to highlight certain some of them to make it easier to read. For example, to highlight weekends, or weekdays.

Step 1: Select the data column or the data range that you want to highlight;

Step 2: Click the "Format" tab from the ribbon;

Step 3: Click the "Conditional formatting" from the drop-down list;

Step 4: On the right of your spreadsheet, you will see the "Conditional format rule" panel. In the "Format rules" box, select "Custom formula is" from the bottom and copy the formula in the next box

=or(weekday(B2)=1, weekday(B2)=7)

In the formula, where B2 is the first cell of the data range, you can change to the first cell of your data range;

• Weekday(B2)=1 is to select Sunday, and
• Weekday(B2)=7 is to select Saturday

To highlight only one day, for example, Sunday, please use the formula:

=weekday(B2)=1

You can change the value to 2 to 6 for weekdays

• =weekday(B2)=1 Sunday
• =weekday(B2)=2 Monday
• =weekday(B2)=3 Tuesday
• =weekday(B2)=4 Wednesday
• =weekday(B2)=5 Thursday
• =weekday(B2)=6 Friday
• =weekday(B2)=7 Saturday

Step 5: If you want a different color from the default color, you can change the color in the "Formatting style" section, e.g., "green";

Step 6: Click the "Done" button, the cells with Saturday and Sunday will be in green.

If it happened that the formula does not work, one common reason is that the weekdays are "text" strings. To convert the workday text string to numbers, please use the formula below:

=MATCH(B2,{"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday"},0)

To highlight Sunday and Saturday that are text string, the formula will become

=OR(MATCH(B2,{"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday"},0)=1, MATCH(B2,{"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday"},0)=7)