Formula Errors

How to Highlight Blank Cells After 7 Days with Excel Conditional Formatting

Ayan Masood Oct 1, 2026 868 views

Question details

The user wants to apply a conditional formatting rule in Excel to automatically highlight blank cells in a specific column if more than seven days have elapsed since a date recorded in another column.

How to Highlight Blank Cells After 7 Days with Excel Conditional Formatting

Product: Microsoft Excel
Device & OS: not provided
Scenario: Tracking overdue tasks or requests where a completion date is missing, and flagging them visually once a 7-day deadline has passed.
Observed behavior: A custom logical formula needs to be applied within the conditional formatting rules to evaluate both the date difference and the cell's blank status.

Before you start

Ensure that the request dates in your reference column (e.g., column B) are formatted as valid dates in Excel, and take note of the exact row number where your data begins.

Solution 1Recommended

Apply a Custom Conditional Formatting Formula

Use a logical formula combining IF, AND, and the TODAY() function to evaluate the date difference and check for empty cells dynamically.

By creating a custom rule, Excel will calculate the difference between the current date and the request date. If the threshold of 7 days is exceeded and the target cell is still empty, the formatting will be triggered.

Select the target range

Highlight the cells in column D where you want the conditional formatting to appear (for example, select D2:D100).

Navigate to New Rule

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

Choose the formula option

In the New Formatting Rule dialog box, click on 'Use a formula to determine which cells to format'.

Enter the logical formula

In the formula input box, type: =IF(AND($B2<>"",$B2+7<TODAY(),$D2=""),TRUE,FALSE). Ensure that the row numbers in the formula ($B2, $D2) match the very first row of your selected range.

Set the highlight format

Click the 'Format' button, navigate to the Fill tab, select a highly visible color such as red or yellow, and click OK twice to apply your new rule.

Apply a Custom Conditional Formatting Formula

Formula Breakdown: The formula checks three conditions simultaneously: Column B must not be empty ($B2<>""), the date in Column B plus 7 days must be earlier than today ($B2+7<TODAY()), and Column D must be empty ($D2="").

Manage Spreadsheets Efficiently

Highlight Overdue Tasks Easily with WPS Spreadsheet

WPS Office provides a highly compatible spreadsheet tool that supports advanced conditional formatting and formulas, making it easy to track deadlines and blank cells just like in Microsoft Excel.

1. Open your file in WPS Spreadsheet: Launch WPS Office and open your task tracking or data spreadsheet.
2. Select your target cells: Highlight the cells in the completion column that you want to monitor for missing data.
3. Create a new formatting rule: Navigate to the Home tab, click 'Conditional Formatting', and select 'New Rule'.
4. Apply the custom formula: Choose the formula option, input your condition =IF(AND($B2<>"",$B2+7<TODAY(),$D2=""),TRUE,FALSE), and select a highlight color.

Fully compatible with Microsoft Excel formulas and conditional formatting features.Lightweight software with a familiar, easy-to-navigate interface.Completely free to use, including advanced data visualization tools.

microsoft office alternative - wps office

Frequently Asked Questions

Why is the wrong cell being highlighted when I apply the conditional formatting formula?

This usually happens when the row number in your formula does not match the first row of your highlighted selection. For example, if you selected D5:D100 before creating the rule, your formula must use $B5 and $D5 instead of $B2 and $D2.

Can I highlight the entire row instead of just the blank cell in column D?

Yes. To highlight the entire row, select your entire data table (e.g., A2:F100) instead of just column D before clicking 'New Rule'. Keep the same formula; the absolute column references ($B2 and $D2) ensure every cell in the row checks the correct columns.

How do I adjust this formula to only count business days instead of calendar days?

To count only business days, replace the simple addition ($B2+7) with the WORKDAY function. Modify the date condition in the formula to check WORKDAY($B2, 5) < TODAY(), which calculates exactly 5 business days from the request date.

Does this formula automatically update tomorrow?

Yes, the TODAY() function is volatile and will automatically update whenever the workbook is opened or recalculated, ensuring your 7-day threshold is always accurate based on the current date.

Excel & Data Problems