Formatting Issues

How to Use Excel Conditional Formatting for Weekdays and Weekends

Tauseeq Magsi Sep 25, 2026 869 views

Question details

The user needs to highlight specific cell values in Excel based on multiple conditions, specifically checking if the corresponding date is a weekday or weekend, along with a numerical threshold.

How to Use Excel Conditional Formatting for Weekdays and Weekends

Product: Excel
Device & OS: not provided
Scenario: Applying complex conditional formatting to a dataset containing dates and numerical values to visually separate weekday and weekend data meeting specific targets.
Observed behavior: The user wants to successfully apply formula-based conditional formatting to dynamically highlight target cells meeting both date-type and value criteria.

Before you start

Ensure your date column contains valid date values recognized by Excel rather than text strings, so the WEEKDAY function can evaluate them correctly.

Solution 1Recommended

Use the AND and WEEKDAY Functions for Conditional Formatting

Combine the AND function with the WEEKDAY function in a new conditional formatting rule to evaluate both the date type and the numerical value simultaneously.

The WEEKDAY function returns a number from 1 to 7 representing the day of the week. By setting the return type to 2 (where Monday is 1 and Sunday is 7), weekends are represented by numbers greater than 5.

Select the target range

Highlight the range of cells you want to format. For example, select your numerical values column, starting from cell B2.

Open Conditional Formatting

Go to the Home tab on the Excel ribbon, click on the 'Conditional Formatting' dropdown button, and select 'New Rule'.

Choose the formula rule type

In the New Formatting Rule dialog box, select 'Use a formula to determine which cells to format' from the list of rule types.

Enter the weekend formula

To highlight values greater than 0 on weekends, enter the formula =AND($B2>0,WEEKDAY($A2,2)>5) in the format values box. If you want values less than 5 on weekends, use =AND($B2<5,WEEKDAY($A2,2)>5).

Enter the weekday formula (Optional)

To apply the less-than-5 rule on weekdays instead, use the formula =AND($B2<5,WEEKDAY($A2,2)<=5).

Apply formatting styles

Click the 'Format' button, choose your desired fill color or font style from the Format Cells dialog, and click 'OK' twice to apply the rule to your selected range.

Use the AND and WEEKDAY Functions for Conditional Formatting

Absolute and Relative References: Make sure to lock the column reference (e.g., $A2) using the dollar sign, but leave the row reference relative so the formula applies correctly down the entire selected column.

Solve it with WPS Spreadsheet

Apply Complex Conditional Formatting Easily with WPS Spreadsheet

WPS Spreadsheet provides a powerful, highly compatible conditional formatting tool that easily supports complex formulas, including combined WEEKDAY and AND functions, completely free of charge.

1. Open your dataset: Launch WPS Spreadsheet and open the file containing your date and value columns.
2. Select the cell range: Highlight the specific cells or rows you wish to apply the weekend or weekday formatting to.
3. Create a new rule: Navigate to the Home tab, click on 'Conditional Formatting', and select 'New Rule'.
4. Apply the custom formula: Choose 'Use a formula to determine which cells to format', enter your WEEKDAY and AND formula, set your custom highlight color, and click 'OK'.

Fully compatible with Microsoft Excel conditional formatting rules and complex functions.Intuitive user interface for creating, managing, and prioritizing multiple formatting rules.Lightweight and fast, maintaining high performance even with large datasets containing complex formulas.Free to use for everyday data analysis and formatting needs.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my WEEKDAY conditional formatting formula highlighting the wrong rows?

This usually happens due to incorrect cell references. Ensure you are using mixed references correctly. You must lock the column (e.g., $A2) but leave the row relative so it adjusts automatically as the conditional formatting rule evaluates down the selected range.

Can I highlight the entire row instead of just a single cell?

Yes. To highlight the entire row, select your entire data table (excluding headers) before creating the conditional formatting rule. Ensure your formula references lock the specific columns being evaluated, such as =AND($B2>0,WEEKDAY($A2,2)>5).

What does the '2' mean in the formula WEEKDAY($A2,2)?

The '2' is the return_type argument in the WEEKDAY function. It configures the function to start counting the week on Monday (as day 1) through Sunday (as day 7). This simplifies identifying weekends by checking if the calculated result is greater than 5 (which represents Saturday and Sunday).

Excel & Data Problems