How to Highlight Past Dates When Another Cell Says "No" in Excel
Question details
The user needs to highlight a date cell in red based on two simultaneous conditions: a corresponding cell contains the text "No", and the date in the cell is strictly before today's date.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Tracking deadlines or task statuses where overdue items need to be visually flagged if they are marked as incomplete.
- Observed behavior
- The user requires a custom conditional formatting formula to evaluate both text and dynamic date criteria at the same time.
Ensure your date column contains valid date values rather than text strings, and identify the exact cell references for your status column and your date column.
Apply a Custom Formula Rule Using the AND Function
Use a custom conditional formatting formula to evaluate both the text condition and the date condition simultaneously.
By combining the AND function with the TODAY() function, you can create a dynamic rule that checks if multiple criteria are met before applying formatting. The TODAY() function automatically updates, ensuring your spreadsheet always highlights overdue items relative to the current date.
Highlight the range of date cells you want to apply the formatting to (for example, B2:B100).
Navigate to the Home tab on the ribbon, click on Conditional Formatting, and select New Rule from the dropdown menu.
In the New Formatting Rule dialog box, select the option labeled "Use a formula to determine which cells to format".
In the formula box, type: =AND(A2="No", B2<TODAY()). Adjust 'A2' to be the first cell of your status column and 'B2' to be the first cell of your selected date column.
Click the Format button, go to the Fill tab, select the color red, and click OK on both dialog boxes to apply the rule.
Easily Apply Conditional Formatting with WPS Spreadsheet
WPS Spreadsheet provides a highly intuitive interface for applying complex conditional formatting rules, allowing you to seamlessly track deadlines and statuses using custom formulas.
- 1. Open your workbook: Launch WPS Spreadsheet and highlight the target date cells in your document.
- 2. Access the New Rule menu: Navigate to the Home tab and click Conditional Formatting > New Rule.
- 3. Input your formula: Select 'Use a formula to determine which cells to format' and input your =AND() formula.
- 4. Apply cell highlighting: Set your desired highlight color in the Format dialog and click OK to save.
Frequently Asked Questions
Why is my conditional formatting highlighting the wrong rows?
This usually happens if your formula uses absolute references (like $A$2) instead of relative references (like A2), or if the active cell when you started creating the rule doesn't match the starting row referenced in your formula.
Can I highlight the entire row instead of just the date cell?
Yes. Select the entire data range (e.g., A2:D100) before creating the rule, and lock the column references in your formula by adding a dollar sign to the column letters, like =AND($A2="No", $B2<TODAY()).
How do I edit an existing conditional formatting rule?
Go to the Home tab, click Conditional Formatting, and select Manage Rules. From the dropdown at the top, select 'This Worksheet', highlight the rule you want to change, and click Edit Rule.
Does the TODAY() function update automatically?
Yes, the TODAY() function is volatile. It will automatically recalculate to the current system date every time you open or modify the spreadsheet, keeping your overdue highlights accurate.
