Formatting Issues

How to Highlight Overdue Dates with Blank Adjacent Cells in Excel

Amos GikundaAmos Gikunda Nov 10, 2026 869 views

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.

How to Highlight Overdue Dates with Blank Adjacent Cells
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.
Before you start

Ensure your date column is properly formatted as 'Date' rather than 'Text', and clear any existing conflicting conditional formatting rules from your target range.

Solution 1Recommended

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.

1
Select the target date range

Highlight the cells containing your dates. For example, click and drag to select J3:J100.

2
Open Conditional Formatting

Navigate to the Home tab on the top ribbon, click on 'Conditional Formatting', and select 'New Rule'.

3
Enter the custom formula

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.

4
Set the highlight format

Click the 'Format' button, choose a fill color (e.g., red or yellow) from the Fill tab, and click OK to apply the rule.

Format Applied: Any dates older than today with an empty adjacent cell will now automatically highlight.
Manage Deadlines Efficiently

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. 1. Open your file in WPS: Launch WPS Spreadsheet and open your task tracking or invoice document.
  2. 2. Select the range: Highlight the date column or entire data rows you wish to monitor.
  3. 3. Apply the rule: Click 'Conditional Formatting' on the Home tab, select 'New Rule', and input your AND formula.
  4. 4. Customize appearance: Choose a striking fill color to ensure overdue items instantly stand out.
Fully compatible with Microsoft Excel (.xlsx) formats and conditional formatting rulesIntuitive formula builder for complex logical conditionsLightweight, fast, and completely free to useFamiliar interface for seamless migration without a learning curve
QA img-9

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.