Formatting Issues

How to Highlight Late or Missing Completion Dates with Conditional Formatting in Excel

Maira Mehtab Sep 20, 2026 868 views

Question details

The user wants to format deadline cells in column D to automatically turn red if the corresponding completion date in column G is either blank (missing) or later than the assigned deadline.

Product: Excel
Device & OS: not provided
Scenario: Tracking project tasks or assignments where overdue deadlines and incomplete statuses need visual highlighting.
Observed behavior: Cells currently do not change color dynamically based on the completion date's relationship with the deadline.

Before you start

Ensure that the data in your deadline column (Column D) and completion date column (Column G) are properly formatted as Dates, and note the starting row of your dataset (e.g., Row 3) before applying the formulas.

Solution 1Recommended

Apply Custom Conditional Formatting Rules

Create two specific formula-based conditional formatting rules to evaluate whether a task is overdue or missing a completion date.

By using the AND and ISBLANK functions, you can tell Excel exactly when to trigger the red formatting. The first formula checks if a deadline exists but the completion date is blank, while the second checks if the logged completion date is greater than the deadline.

Select the target range

Highlight all the cells in the deadline column (Column D) that you want to format, starting from your first data row (e.g., D3:D100).

Open Conditional Formatting

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

Set up the rule for blank completion dates

Choose 'Use a formula to determine which cells to format'. In the formula box, enter =AND(ISBLANK(G3),NOT(ISBLANK(D3))). Click 'Format', select a red fill color, and click 'OK'.

Set up the rule for late completion dates

Repeat the process to create a second new rule. This time, enter the formula =AND(NOT(ISBLANK(G3)),G3>D3). Set the format to a red fill color, and click 'OK' to apply.

Verify Cell References: Make sure the row numbers in your formulas (e.g., G3 and D3) perfectly match the very first row of your highlighted selection to ensure the formatting aligns correctly across the entire column.

Manage Spreadsheets Easily

Track Deadlines Effortlessly in WPS Spreadsheet

WPS Spreadsheet features robust conditional formatting capabilities that perfectly support custom formulas, allowing you to seamlessly track overdue or missing tasks just like in Microsoft Excel.

1. Open your file in WPS Spreadsheet: Launch WPS Office and open your task tracking spreadsheet.
2. Select your deadline column: Click and drag to select the deadline dates you wish to monitor.
3. Create a new formatting rule: Go to the Home tab, click 'Conditional Formatting', choose 'New Rule', and select the formula option.
4. Apply your formulas: Enter the custom formulas for late or missing dates, set your desired highlight color, and save.

Fully compatible with Microsoft Excel formulas and .xlsx files.Advanced conditional formatting rules for visual data management.Free, lightweight, and user-friendly spreadsheet interface.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my conditional formatting applying to the wrong rows?

This usually occurs if the row reference in your formula (like G3) does not match the first row of the range you selected before creating the rule. To fix this, go to Conditional Formatting > Manage Rules, and edit the formula so the row number matches the top cell in the 'Applies to' range.

Can I combine both conditions into a single formatting rule?

Yes, you can use the OR function to combine them. Use this single formula: =OR(AND(ISBLANK(G3),NOT(ISBLANK(D3))), AND(NOT(ISBLANK(G3)),G3>D3)). This will apply the red fill if either the completion date is blank or if it is past the deadline.

How do I clear the conditional formatting if I make a mistake?

Select the cells with the formatting you want to remove, navigate to Home > Conditional Formatting > Clear Rules, and then select 'Clear Rules from Selected Cells'.

Document & File Issues