Formatting Issues

How to Keep Today's Date Red Until Adjacent Cell is Filled in Excel

John Wilson Oct 10, 2026 869 views

Question details

The user needs a conditional formatting formula to highlight dates due today or earlier in red, but only if the adjacent status cell remains empty.

How to Keep Today's Date Red Until Adjacent Cell is Filled in Excel

Product: Excel
Device & OS: not provided
Scenario: Tracking due dates where an action needs to be completed and marked in an adjacent column to remove the overdue highlight.
Observed behavior: The user requires a specific conditional formatting formula that evaluates both the date cell against today's date and the blank status of an adjacent cell simultaneously.

Before you start

Before applying the formula, verify that your date column contains valid date values (rather than plain text) and identify the specific column letters for your dates and their corresponding status cells.

Solution 1Recommended

Apply the AND + ISBLANK Formula in Conditional Formatting

Use a custom formula rule to format the date cell red if the date is today or earlier, provided the adjacent cell is still blank.

Select the target range

Highlight the range of date cells you want to format. For example, select cells from G13 down to G100.

Create a new rule

Go to the Home tab on the ribbon, click on 'Conditional Formatting', and select 'New Rule' from the drop-down menu.

Enter the formula

Choose 'Use a formula to determine which cells to format'. In the formula box, enter =AND($G13<=TODAY(),ISBLANK($H13)) assuming column G contains dates and column H is the adjacent cell.

Set the format color

Click the 'Format' button, switch to the Fill tab, choose the color red, and click OK twice to apply the formatting rule.

Apply the AND + ISBLANK Formula in Conditional Formatting

Relative vs Absolute Referencing: Make sure to use the correct absolute referencing (the $ sign before the column letter) so the rule applies correctly across your selected range. Because the formula already checks the date condition, you do not need to use NOT(ISBLANK()) for the date cell itself.

WPS Spreadsheet Solutions

Easily Apply Conditional Formatting in WPS Spreadsheet

WPS Spreadsheet offers full support for advanced conditional formatting formulas, allowing you to track due dates seamlessly. It provides a user-friendly interface for managing your daily tasks effectively.

1. Select Target Cells: Open your workbook in WPS Spreadsheet and select the range of date cells you want to monitor.
2. Open Conditional Formatting: Navigate to the Home tab on the top ribbon, click on Conditional Formatting, and choose New Rule.
3. Apply Formula and Format: Choose the option to use a formula, enter =AND($G13<=TODAY(),ISBLANK($H13)), set your desired red fill color under the Format menu, and click OK.

Native support for Microsoft Excel formats (.xlsx, .xls) and full formula compatibility.Advanced conditional formatting capabilities to track deadlines and tasks automatically.Free, lightweight, and user-friendly alternative to heavy spreadsheet applications.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my conditional formatting applying to the wrong rows?

This usually happens when the formula references the wrong starting row. Ensure that the row number in your formula (e.g., $G13) exactly matches the first row of your currently selected range.

Can I highlight the entire row instead of just the date cell?

Yes. To highlight the entire row, select your whole data range before creating the rule, rather than just the date column. Ensure the column references in your formula are locked with a dollar sign (e.g., $G13 and $H13).

How do I remove the red formatting if the date is in the future?

The formula =AND($G13<=TODAY(),ISBLANK($H13)) only triggers for today or dates in the past. Future dates will automatically not be highlighted in red because they do not meet the <=TODAY() condition.

Does this conditional formatting formula work in WPS Spreadsheet?

Yes, the exact same formula and Conditional Formatting logic work perfectly in WPS Spreadsheet to track due dates and blank adjacent cells.

Document & File Issues