How to Automatically Highlight Weekends in Google Sheets Using Conditional Formatting

When managing project timelines, shift schedules, attendance registers, or financial ledgers in Google Sheets, manually formatting Saturday and Sunday cells is repetitive and prone to errors whenever dates change. By taking advantage of Google Sheets’ conditional formatting engine combined with the WEEKDAY function, you can automatically shade weekend cells or entire calendar rows dynamically.

How the WEEKDAY Function Determines Weekend Dates

The WEEKDAY function inspects a valid date and returns an integer corresponding to the day of the week. The syntax is:

WEEKDAY(date, [type])

The second parameter (type) determines how days are numbered. Using type 2 is the most intuitive calculation for conditional formatting:

• type 1 (default): Sunday = 1, Monday = 2 … Saturday = 7.
• type 2 (recommended): Monday = 1, Tuesday = 2 … Saturday = 6, Sunday = 7.

Because Saturday and Sunday evaluate to 6 and 7 under type 2, any date where WEEKDAY(date, 2) > 5 is automatically identified as a weekend day.

Step-by-Step Guide: How to Highlight Weekend Date Cells

If you have a column or row of dates and want only the specific date cells shaded:

1. Open your spreadsheet in Google Sheets.
2. Click and drag to select the cell range containing your dates (for example, A2:A31).
3. Click Format in the top menu bar and select Conditional formatting to open the sidebar.
4. In the Format cells if dropdown menu, scroll to the bottom and select Custom formula is.
5. In the formula input field, type the following expression:

   =AND(A2<>"", WEEKDAY(A2, 2) > 5)

   (Replace A2 with the very first top-left cell in your selected range.)
6. Under the Formatting style section, choose your preferred fill colour (such as a light grey or subtle pastel yellow).
7. Click Done. Google Sheets will instantly highlight every Saturday and Sunday in your list.

How to Highlight Entire Rows Based on Weekend Dates

In project trackers and timesheets, shading the entire horizontal row across multiple data columns (e.g., Date, Employee Name, Shift, Hours) makes scanning easier. This requires adding a dollar sign ($) before the column letter to lock evaluation to the date column:

1. Select your entire data table, excluding headers (for example, select A2:E31).
2. Open the Conditional formatting rules panel via Format > Conditional formatting.
3. Set the rule to Custom formula is.
4. Enter the locked-column formula:

   =AND($A2<>"", WEEKDAY($A2, 2) > 5)

5. Select a contrasting fill colour and text styling.
6. Click Done. The entire row from column A through column E will now highlight whenever the date in column A falls on a Saturday or Sunday.

Why the AND(A2<>””, …) Check is Critical

In Google Sheets, empty or blank cells are mathematically evaluated as the number zero. In computer date systems, date zero maps to 30 December 1899—which happened to be a Saturday! If you use a simple formula like =WEEKDAY(A2, 2) > 5 without checking for empty values, Google Sheets will erroneously highlight every empty cell in your table. Wrapping the condition inside AND(A2<>"", ...) ensures that empty rows remain completely unformatted.

Customising the Weekend Days for Different Work Schedules

If your organisation operates on non-standard work weeks (such as Friday and Saturday weekends common in parts of the Middle East), you can adjust the formula conditions:

• Friday and Saturday Weekends: Use =AND(A2<>"", OR(WEEKDAY(A2)=6, WEEKDAY(A2)=7)) under standard type 1 numbering.
• Sunday-Only Weekend: Use =AND(A2<>"", WEEKDAY(A2, 2) = 7) to highlight Sundays exclusively.

Automating weekend formatting with dynamic formulas keeps your project tracking sheets clean, professional, and maintenance-free, automatically adapting whenever project start dates shift.