How to Highlight Overdue Dates with Blank Adjacent Cells in Excel
Question details
The user needs to dynamically highlight a specific date cell or an entire row when the date has passed and the adjacent status cell remains empty.

- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Tracking deadlines, tasks, or invoices where completion status is left blank, and overdue items need visual emphasis.
- Observed behavior
- Requires a formula-based conditional formatting rule to automatically color-code items past the current date only if the corresponding status cell lacks data.
Ensure your date column is properly formatted as 'Date' rather than 'Text', and clear any existing conflicting conditional formatting rules from your target range.
Apply Conditional Formatting Formula to Date Cells
Use a custom formula combining AND, TODAY, and ISBLANK to highlight only the date cells that are past due and lack a completion status.
By combining logical functions, you can instruct the spreadsheet to verify two conditions simultaneously: whether the date is earlier than today, and whether the adjacent status cell contains no data.
Highlight the cells containing your dates. For example, click and drag to select J3:J100.
Navigate to the Home tab on the top ribbon, click on 'Conditional Formatting', and select 'New Rule'.
Choose 'Use a formula to determine which cells to format'. In the formula box, enter =AND(J3<TODAY(), ISBLANK(K3)). If column K contains formulas that return empty strings, use =AND(J3<TODAY(), K3="") instead.
Click the 'Format' button, choose a fill color (e.g., red or yellow) from the Fill tab, and click OK to apply the rule.
Highlight the Entire Row for Overdue Items
Lock the column references in your formula to extend the conditional formatting across the entire row of data.
Track Overdue Dates Easily with WPS Spreadsheet
WPS Office Spreadsheet provides intuitive and powerful conditional formatting tools to seamlessly manage task deadlines, automate your workflow, and color-code your data instantly.
- 1. Open your file in WPS: Launch WPS Spreadsheet and open your task tracking or invoice document.
- 2. Select the range: Highlight the date column or entire data rows you wish to monitor.
- 3. Apply the rule: Click 'Conditional Formatting' on the Home tab, select 'New Rule', and input your AND formula.
- 4. Customize appearance: Choose a striking fill color to ensure overdue items instantly stand out.

Frequently Asked Questions
Why is my conditional formatting highlighting blank date cells as overdue?
Spreadsheets treat entirely blank cells as zero (representing January 0, 1900), which is always less than TODAY(). To prevent empty date cells from being highlighted, add an extra condition to ensure the date cell is not blank: =AND(J3<>"", J3<TODAY(), K3="").
How do I change the highlight color for my overdue dates later?
Go to the Home tab, click Conditional Formatting, and choose 'Manage Rules'. Select your existing rule, click 'Edit Rule', and then use the 'Format' button to pick a different fill color or font style.
Will the TODAY() formula update automatically tomorrow?
Yes. The TODAY() function is volatile, meaning it recalculates automatically. Your conditional formatting will dynamically update to reflect the current system date every time you open the workbook or whenever the sheet recalculates.




