Highlight Weekends in Google Sheets

GiGiKing·March 28, 2024·0 comments

Are you looking to highlight weekends in a calendar, a column in a table, or the taskbar area of a Gantt chart in Google Sheets? Well, you’re in luck! In this article, we will explore different scenarios and provide you with the techniques to achieve this.

Before we dive into the examples, let’s take a moment to look at the table below. It contains numbers representing days of the week.

Table

We may need to use these numbers in a formula to highlight specific weekends. Now, let’s explore different scenarios and how to handle them.

Highlight Weekends in a Column

Let’s consider an example where we have sales dates in column A and sales quantities in column B. We want to highlight the weekends in column A (specifically, Saturday and Sunday).

To achieve this, we can use the following highlight rule:

=AND(OR(WEEKDAY(A2)=1, WEEKDAY(A2)=7), ISDATE(A2))

To apply this rule in the range A2:A, follow these steps:

  1. Click on Format > Conditional formatting.
  2. Enter A2:A under “Apply to range.”
  3. Under “Format rules,” select “Custom formula is,” and enter the above formula.
  4. Under the “Formatting style,” pick/apply your preferred style and click “Done.”

Follow-Up Questions:

  1. What changes should I make to highlight specific weekends such as Friday and Saturday in columns?
    Replace =1 with =6 in the formula (representing Friday).
=AND(OR(WEEKDAY(A2)=6, WEEKDAY(A2)=7), ISDATE(A2))
  1. If you want to highlight only a single weekend day such as Sunday only?
    You don’t need the OR logical test and only require one WEEKDAY test.
=AND(WEEKDAY(A2)=1, ISDATE(A2))
  1. How to highlight the entire rows matching weekends in Google Sheets?
    There are two changes required:
  • In the “Apply to range,” replace A2:A with the range you want to highlight (e.g., A2:B).
  • In the formula, place a dollar sign in front of the column letter in cell references. So A2 will become $A2. Please see the below formula which highlights Saturday and Sunday weekends, but the entire row in the selected range.
=AND(OR(WEEKDAY($A2)=1, WEEKDAY($A2)=7), ISDATE($A2))

Row Range

To learn more about the formula, you can check out our article on “Combined Use of IF, AND, and OR Logical Functions in Google Sheets.”

Highlight Weekends in a Calendar

Google Sheets offers various calendar templates, including our very own. Highlighting weekends in a calendar can be challenging due to different formula approaches and layout settings for each calendar.

Our formula (conditional format rule) will work if the days within the calendar are actual dates. You can use the ISDATE function to test whether a cell value is a date.

Another criterion is a month name in any cell in the sheet. If your template satisfies this, you can use the same formula as above, with one addition.

Example:

=AND(OR(WEEKDAY(C4)=1, WEEKDAY(C4)=7), ISDATE(C4), MONTH(C4)=MONTH($H$1&1))

In the above example, the “Apply to range” (highlight range) is C4:I22. So, in the formula, you should use cell C4 as a reference.

The other change in the formula is the use of MONTH(C4)=MONTH($H$1&1). It checks whether the month number of the dates in the calendar matches the month number in cell H1. MONTH($H$1&1) converts the month text in cell H1 to the month number.

Calendar

This criterion is necessary because most calendars may have hidden dates from the previous month in the first row (week), as well as hidden dates from the next month in the last row (week). We don’t want the formula to highlight those cells.

Notes:

  • A calendar may already have several formatting. You may need to drag and drop the rules according to your priority. To do that, hover your mouse pointer over the rule in question, click on the vertical dots on the left, and drag.
  • To highlight specific weekends in a Google Sheets calendar, please refer to the instructions provided earlier.

Highlight Weekends in a Gantt Chart Timescale

To highlight weekends in the taskbar area of a Gantt chart, you need to match weekends in the timescale. This works if the values in the timescale are dates, which you can check using the ISDATE function.

Example:

In the following example, the “Apply to range” is E4:Z20. We use the formula below to highlight weekends in the taskbar area.

=AND(OR(WEEKDAY(E$3)=1, WEEKDAY(E$3)=7), ISDATE(E$3))

Note: To highlight specific weekends in a Gantt chart, please refer to the instructions provided earlier.

Resources

With the examples provided, you should now be able to highlight weekends in specific cells, entire columns, and rows in Google Sheets. If you’d like to deepen your conditional formatting expertise, here are some related resources:

  1. Highlighting Today and N Cells Below in Google Sheets Calendar
  2. How to Highlight Next N Working Days in Google Sheets
  3. Highlight Earliest Events Based on Date Column in Google Sheets
  4. Date-Related Conditional Formatting Rules in Google Sheets
  5. How to Highlight Cells Based on Expiry Date in Google Sheets
  6. How to Highlight Recurring Events or Payment Dates in Google Sheets

For more informative articles and tips on Google Sheets and other topics, visit Crawlan.com.

GiGiKing

Related posts