Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to highlight weekend dates in Excel and Google Sheets
Topic:Conditional formattingExcel & Google Sheets
=WEEKDAY(A2,2)>5Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1Select the date range, e.g. A2:A30.
- 2Open Conditional Formatting and choose 'Use a formula'.
- 3Enter =WEEKDAY(A2,2)>5 — with mode 2, Monday=1 through Sunday=7, so values 6 and 7 are Saturday and Sunday. Choose a highlight color and save.
Tips
- Use WEEKDAY mode 2 (Monday=1, Sunday=7) for cross-region consistency. Mode 1 (Sunday=1) is US-default and breaks on European sheets.
- When the formula is highlighting blank cells too, wrap with ISNUMBER: =AND(ISNUMBER(A2),WEEKDAY(A2,2)>5) so only real date values are evaluated.
- Make the reference RELATIVE (A2, not $A$2) when applying the rule to a column — Conditional Formatting then evaluates each row against its own date.
- To highlight an ENTIRE ROW based on the date column, anchor the column: =WEEKDAY($A2,2)>5 (note the $ on the column, not the row).
- For separate Saturday and Sunday colors, create two rules: =WEEKDAY(A2,2)=6 for Saturday and =WEEKDAY(A2,2)=7 for Sunday, with different fills.
- To exclude holidays too, add a NOT MATCH check: =AND(WEEKDAY(A2,2)>5,ISNA(MATCH(A2,Holidays,0))) where Holidays is a named range — weekends become highlighted only if they are not also listed as holidays.
Working on a sheet you inherited? Run the Auditor on the whole file first — it flags every #REF!, #N/A, broken column pattern, and inconsistent formula in seconds, free, no signup.
Frequently asked
The formula is highlighting non-date cells. How do I prevent that?
Wrap with AND and ISNUMBER: =AND(ISNUMBER(A2),WEEKDAY(A2,2)>5) so only numeric date values trigger the highlight and text or empty cells are ignored.
How do I highlight the entire row, not just the date cell?
Select the full row range, then in the formula anchor the column reference: =WEEKDAY($A2,2)>5. The $ before A keeps the column fixed while the row evaluates per row.
How do I highlight ONLY Saturday or ONLY Sunday?
Use =WEEKDAY(A2,2)=6 for Saturday-only and =WEEKDAY(A2,2)=7 for Sunday-only. Create two separate rules if you want distinct colors.
How do I exclude public holidays from the weekend highlight?
Combine with MATCH: =AND(WEEKDAY(A2,2)>5,ISNA(MATCH(A2,Holidays,0))) where Holidays is a named range listing your holiday dates. The cell highlights only if it is a weekend AND not in the Holidays list.
Does this work the same in Google Sheets?
Yes — Google Sheets supports WEEKDAY identically. Go to Format → Conditional formatting → Custom formula and paste =WEEKDAY(A2,2)>5.
My weekend dates are stored as text — does this still work?
No — WEEKDAY needs real date values. Convert text dates first with DATEVALUE: =WEEKDAY(DATEVALUE(A2),2)>5, or fix the source by selecting the column and Data → Text to Columns → Finish to coerce text dates to real dates.
More on Conditional formatting
See all →How-to
How to highlight duplicate values
Highlight duplicate cells automatically with Conditional Formatting using a COUNTIF rule — no formulas to copy manually.
How-toHow to highlight cells greater than a value
Use Conditional Formatting with a simple greater-than rule to automatically color cells that exceed a threshold value.
How-toHow to highlight the highest value in a range
Color the maximum value in a range automatically using a Conditional Formatting rule based on the MAX function.
How-toHow to highlight the lowest value in a range
Color the minimum value in a range using a Conditional Formatting rule built on the MIN function.
How-toHow to highlight blank cells
Use conditional formatting with ISBLANK to automatically highlight empty cells in your spreadsheet for easy visual identification.
How-toHow to highlight cells that contain specific text
Use conditional formatting with SEARCH or ISNUMBER(SEARCH()) to highlight any cell containing a specific word or phrase automatically.