How to Prevent Excel Conditional Formatting from Treating Blanks as Saturdays
Question details
The user needs to prevent Excel's conditional formatting rules from incorrectly applying Saturday formatting to blank date cells.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Applying weekday-based conditional formatting to a range of cells that includes empty cells.
- Observed behavior
- Blank cells are treated as zero, which Excel's 1900 date system interprets as a Saturday, causing the formatting rule to trigger incorrectly on empty cells.
Identify the specific range of cells where your conditional formatting is applied and take note of the exact WEEKDAY formula currently being used.
Update the Conditional Formatting Formula to Exclude Blanks
Modify your existing WEEKDAY formula by wrapping it in an AND function that explicitly checks if the cell is not blank.
Because Excel's 1900 date system evaluates a blank cell as a zero, and zero corresponds to a Saturday, a standard WEEKDAY formula will format empty cells. Adding a non-blank condition solves this issue by ensuring the rule only triggers when a date is actually present.
Highlight the range of cells where the conditional formatting is currently applied.
Go to the 'Home' tab on the ribbon, click 'Conditional Formatting', and select 'Manage Rules'.
Select your existing weekday rule from the list and click the 'Edit Rule' button.
Change the formula to include the non-blank check. For example, if checking column D starting at row 3, use =AND($D3<>"",WEEKDAY($D3)=7).
Click 'OK' to close the Edit Formatting Rule dialog, and then click 'Apply' to save the changes to your spreadsheet.
Apply Conditional Formatting Easily with WPS Office
WPS Spreadsheet offers full support for advanced conditional formatting formulas, allowing you to highlight dates, exclude blanks, and manage complex rules with an intuitive interface.
- 1. Open your file: Launch WPS Office and open your spreadsheet document.
- 2. Select the range: Highlight the target cell range where you want to apply the date formatting.
- 3. Create a new rule: Navigate to Home > Conditional Formatting > New Rule.
- 4. Enter the formula: Choose 'Use a formula to determine which cells to format' and enter =AND($D3<>"",WEEKDAY($D3)=7).
- 5. Set format and save: Click 'Format' to set your desired highlight color, then click OK to apply the rule.
Frequently Asked Questions
Why does Excel think a blank cell is a Saturday?
Excel uses the 1900 date system where dates are stored as sequential serial numbers. A blank cell is evaluated as 0 in formulas. In this system, the serial number 0 resolves to January 0, 1900, which Excel calculates as a Saturday.
How do I ignore blank cells in other conditional formatting rules?
You can generally ignore blank cells by adding an AND condition to your formula, such as =AND(A1<>"", [Your Original Condition]). Alternatively, you can create a new rule specifically for blank cells, set it to have no formatting, and check the 'Stop If True' box in the Rules Manager.
Does this formula work for Sundays as well?
Yes. To format for Sundays while ignoring blanks, simply change the WEEKDAY value from 7 to 1. The formula would be =AND($D3<>"",WEEKDAY($D3)=1).
