Formatting Issues

How to Set Excel Conditional Formatting for 90-Day and 180-Day Due Date Alerts

Camila Milosovich Sep 30, 2026 869 views

Question details

The user needs to use Excel conditional formatting to highlight an entire row when a due date is 90 days or 180 days past due, ensuring the rules do not overlap or produce #NAME? errors.

How to Set Excel Conditional Formatting for 90-Day and 180-Day Due Date Alerts

Product: Excel
Device & OS: not provided
Scenario: Setting up automated visual alerts for overdue deadlines, invoices, or tasks using spreadsheets.
Observed behavior: Basic formulas may cause the 90-day rule to overlap and overwrite the 180-day rule, or produce a #NAME? error if formatted incorrectly.

Before you start

Ensure that your due date column contains valid date values rather than text strings, as the TODAY() function requires numerical date formats to calculate the time difference accurately.

Solution 1Recommended

Apply Mutually Exclusive Formula Rules for Due Dates

Use the AND and ISNUMBER functions to create precise date ranges that prevent formatting overlaps.

To highlight entire rows without the 90-day rule overriding the 180-day rule, you need to use an absolute column reference (like $B2) and set a strict date bracket for the first condition.

Select the Data Range

Highlight your entire dataset, excluding the headers. For example, select A2:F100.

Create the 90-Day Rule

Go to Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Enter the formula: =AND(ISNUMBER($B2),TODAY()>=$B2+90,TODAY()<$B2+180). Replace $B2 with your actual due date column. Click Format to choose your highlight color (e.g., yellow) and click OK.

Create the 180-Day Rule

Repeat the process to add a second rule. Use the formula: =AND(ISNUMBER($B2),TODAY()>=$B2+180). Click Format to choose a different color (e.g., red) and click OK.

Manage Rule Precedence

Go to Conditional Formatting > Manage Rules. Ensure the 180-day rule is placed above the 90-day rule in the list. Check the 'Stop If True' box for both rules and click Apply.

Apply Mutually Exclusive Formula Rules for Due Dates

Avoid #NAME? Errors: Ensure you spell the TODAY() function correctly and include the empty parentheses. Missing these will trigger a #NAME? error.

Manage Deadlines Easily

Track Due Dates with WPS Spreadsheet

WPS Spreadsheet fully supports advanced conditional formatting formulas, allowing you to seamlessly highlight rows and manage overdue tasks. It is fully compatible with Microsoft Excel formats.

1. Open your file: Launch WPS Spreadsheet and open your task or invoice tracking document.
2. Access Conditional Formatting: Select your data range, navigate to the Home tab, and click on Conditional Formatting.
3. Apply Formulas: Choose 'New Rule' > 'Use a formula', enter your date tracking formulas, and set your desired fill colors.

Fully compatible with Microsoft Excel formatting and formulas (.xlsx).Advanced Conditional Formatting manager for complex rule sets.Lightweight application that runs smoothly on any device.Free to use with a familiar, user-friendly interface.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my conditional formatting rule returning a #NAME? error?

A #NAME? error occurs when Excel does not recognize text in a formula. This usually happens if there is a typo in the function name (e.g., typing TODY instead of TODAY) or if you forget to include the empty parentheses after TODAY().

How do I ensure the entire row highlights instead of just the due date cell?

You must use a mixed cell reference in your formula by placing a dollar sign ($) before the column letter (e.g., $B2). This locks the condition to the due date column while allowing the row number to adjust as the rule evaluates across the dataset.

Why does my 90-day alert overwrite the 180-day alert?

If a date is 180 days past due, it is technically also 90 days past due, causing both rules to trigger. To fix this, use the AND function to restrict the 90-day rule to an exact range (e.g., between 90 and 179 days), or move the 180-day rule to the top of the Conditional Formatting Rules Manager and check 'Stop If True'.

Excel & Data Problems