Formatting Issues

How to Highlight Dates Within Nine Weeks in Excel Using Conditional Formatting

Rana Garcia Oct 22, 2026 869 views

Question details

The user needs a method to highlight cells in a scheduled shipping date column if the date is within nine weeks of today, but only when the corresponding steel order date cell is blank.

How to Highlight Dates Within Nine Weeks Using Conditional Formatting in Excel

Product: Excel
Device & OS: not provided
Scenario: Tracking scheduled shipping dates and identifying urgent deadlines where the related steel order has not yet been placed.
Observed behavior: Cells need to automatically change background color based on a multiple-condition formula evaluating both a date calculation and an empty cell check.

Before you start

Ensure your scheduled shipping dates are formatted as valid Date values in Excel (not as text), otherwise the formula will not be able to accurately calculate the difference between the shipping date and today's date.

Solution 1Recommended

Use the AND and TODAY Functions in a Custom Formatting Rule

Create a conditional formatting rule using a custom formula that evaluates two conditions simultaneously: if the date is within 63 days (nine weeks) and if the adjacent target cell is empty.

To achieve multiple conditions in Conditional Formatting, you can combine the AND() function with the TODAY() function. The formula will return TRUE only when both criteria are met, triggering the highlight formatting.

Select the target column

Click and drag to select the cells in your scheduled shipping date column that you want to format (for example, start your selection from cell D2).

Open Conditional Formatting

Navigate to the 'Home' tab on the ribbon, click on 'Conditional Formatting' in the Styles group, and select 'New Rule' from the dropdown menu.

Enter the custom formula

Choose the option 'Use a formula to determine which cells to format'. In the formula box, enter `=AND($D2<=TODAY()+63,$E2="")`. Adjust $D2 to your first selected shipping date cell and $E2 to the corresponding order date cell.

Define the visual format

Click the 'Format' button, switch to the 'Fill' tab, and choose a highlight color (such as yellow or red). Click 'OK'.

Apply and save

Click 'OK' again on the New Formatting Rule dialog to apply the rule. The matching dates will now be highlighted automatically.

Use the AND and TODAY Functions in a Custom Formatting Rule

Understanding Reference Locking: Using the dollar sign ($) before the column letter locks the column reference. This ensures Excel evaluates the specific columns for shipping and order dates, while the relative row numbers allow the rule to check each row individually.

Advanced Spreadsheet Formatting

Use WPS Spreadsheet to Track Deadlines Automatically

WPS Spreadsheet provides powerful and intuitive conditional formatting features, fully supporting advanced functions like AND, IF, and TODAY to help you manage complex shipping and order tracking schedules effortlessly.

1. Open your data file: Launch WPS Spreadsheet and open your order tracking workbook.
2. Highlight the date range: Select the scheduled shipping dates column starting from the first data row.
3. Create a new rule: Go to 'Home' > 'Conditional Formatting' > 'New Rule', then select the 'Use a formula' option.
4. Apply the logic: Input your formula `=AND($D2<=TODAY()+63,$E2="")`, set your desired highlight color, and click OK.

100% compatible with Microsoft Excel conditional formatting formulas and rules.Intuitive rule manager to easily edit, prioritize, or clear formatting conditions.Free to use, lightweight, and handles large datasets without lag.

microsoft office alternative - wps office

Frequently Asked Questions

How do I apply this highlight to the entire row instead of just the date cell?

To highlight the entire row, select your entire data table (e.g., A2:F100) before creating the conditional formatting rule. Since the formula uses absolute column references like $D2 and $E2, Excel will format the entire row based on the values in columns D and E.

Can I highlight dates that are exactly nine weeks away, rather than within nine weeks?

Yes. Simply change the less-than-or-equal-to operator (<=) in your formula to an equal sign (=). The modified formula will look like `=AND($D2=TODAY()+63,$E2="")`, which only highlights dates exactly 63 days from today.

Why isn't my formatting rule updating when the day changes?

The TODAY() function recalculates automatically when a worksheet is opened or modified. If you leave the workbook open overnight, it might not update automatically. Press F9 on your keyboard to manually recalculate the workbook and refresh the date formatting.

What if I need to check for past due dates as well?

The current formula `$D2<=TODAY()+63` highlights any date before 63 days from now, which includes dates in the past. If you only want future dates within 63 days, update the formula to add a third condition: `=AND($D2<=TODAY()+63, $D2>=TODAY(), $E2="")`.

Document & File Issues