Excel Conditional Formatting Formula for Due Dates Explained

In Excel for Microsoft 365 on Windows, select the cells to format, choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format, and enter =AND(ISNUMBER($B2),$B2<TODAY()) to highlight overdue due dates in column B. The formula assumes the first date is in B2; use a format such as a red fill, and adjust the column and starting row to match your sheet.

What the due-date formula checks

The formula checks two conditions: the cell contains a number, and its value is earlier than today. Excel stores real dates as serial numbers, so ISNUMBER helps prevent blank cells and text from being treated as deadlines. TODAY() returns the current date, without a time. As the date changes, the rule can move a task from upcoming to overdue without you editing the formula.

The dollar sign before B locks the date column, while the row number remains relative. That distinction matters when a rule applies to more than one cell: each row checks its own date in column B. In the rule dialog, choose Format to set a fill, font, or border, then confirm the rule and inspect the Applies to range.

For example, if B2 contains October 31 and today is November 2, the formula evaluates as true and Excel applies the chosen format. If B2 is blank, or contains a text note such as “not scheduled,” ISNUMBER returns false and the cell is not highlighted.

Set up the rule for a date column

Put due dates in one column, such as B, with a header in B1 and the first date in B2.
Select the range to format, for example B2:B100. Select only the date cells if you want only the dates colored.
On the Home tab, choose Conditional Formatting > New Rule. Select Use a formula to determine which cells to format.
Enter the formula, choose Format, and pick the visual style. Click OK to save the rule.
Open Conditional Formatting > Manage Rules to review or edit the formula and its Applies to range.

When selecting B2:B100, use the formula shown above: its row number, 2, matches the first row of the selected range. If you instead select B5:B100, change the formula to start with row 5, such as =AND(ISNUMBER($B5),$B5<TODAY()). The same alignment principle applies when formatting whole task rows: select the task range beginning on row 2 and keep the formula’s row reference at 2.

In Excel for Mac, the Conditional Formatting controls are on the Home tab as well, although dialog layouts can vary slightly by release. The equivalent formula-based rule is available through the new-rule options. Excel for the web also offers conditional formatting, but some controls and rule-management details may look different from the Windows desktop app.

Formulas for overdue, due today, and upcoming dates

Choose a rule based on what you want readers of the sheet to notice. The examples below assume dates begin in B2, and each formula should be created with a selected range whose first row is row 2. Choose distinct formats so that categories are easy to distinguish.

Overdue: With due dates in B2:B100, use =AND(ISNUMBER($B2),$B2<TODAY()). Dates before today are highlighted; dates due today are not overdue under this definition.
Due today: With due dates in B2:B100, use =AND(ISNUMBER($B2),$B2=TODAY()). This flags dates equal to today.
Due within the next seven calendar days, including today: With due dates in B2:B100, use =AND(ISNUMBER($B2),$B2>=TODAY(),$B2<=TODAY()+7). This includes today and dates through seven days from today, so it can cover eight calendar dates when today is counted.
Overdue tasks that are not complete: With due dates in B2:B100 and task statuses in C2:C100, use =AND(ISNUMBER($B2),$B2<TODAY(),$C2<>"Complete"). A completed task is excluded when its status cell contains exactly Complete; spelling and extra spaces can affect the match.

To color an entire task row rather than just the due-date cell, select the rows across the table, such as A2:D100, and use a formula that still checks the date column. With due dates in B and the first selected row at 2, the overdue formula remains =AND(ISNUMBER($B2),$B2<TODAY()). The locked column keeps the test in B, while the changing row applies the correct deadline to each task.

How references and Applies to affect the result

Conditional formatting formulas are evaluated relative to the top-left cell of the Applies to range. If the range is A2:D100, Excel evaluates the formula for A2 first, then adjusts its relative row references as it applies the rule to later rows. This is why $B2 is useful for row highlighting: the column stays B, but row 2 becomes row 3, row 4, and so on.

A formula such as =AND(ISNUMBER($B$2),$B$2<TODAY()) locks both the column and row. Applied to many rows, it keeps checking B2 for every task, which usually makes every row share the same result. Conversely, B2 without a dollar sign on the column can shift across columns when applied to a multi-column range. Reference styles should match the shape of the selected range.

Check Applies to in Manage Rules if only part of the table changes color. The range should include every intended date cell or row, but it should not include a header unless you specifically want the header evaluated. A formula can be correct and still appear ineffective if the selected range starts on a different row from the formula’s row reference.

Dates with times, blanks, and text values

Some systems place a time in the same cell as a date. In that case, a deadline stored as today at 3:00 p.m. is numerically later than TODAY(), which represents midnight at the start of today. A simple equality test against TODAY() may therefore miss it. Use INT to compare only the date portion when you want the entire calendar day treated as due today.

With date-and-time values in B2:B100 and the first selected row at 2, use =AND(ISNUMBER($B2),INT($B2)=TODAY()) to highlight any time on today’s date. To flag date-and-time values from earlier calendar days as overdue, use =AND(ISNUMBER($B2),INT($B2)<TODAY()). INT removes the fractional time portion for the comparison; it does not change the value stored in the cell.

Blank cells deserve attention because an empty due-date field should usually remain unformatted. ISNUMBER provides a clear guard against blanks and text. If a cell looks like a date but fails the test, its contents may be text rather than an Excel date value. Changing its number format alone does not convert text into a true date; convert the underlying value or re-enter it as a date before relying on date comparisons.

Rule conflicts and common troubleshooting

When several conditional-formatting rules apply to the same cells, one rule’s format can obscure another’s. For instance, a broad “due within seven days” rule may overlap with “due today.” Use different visual properties where possible, such as a colored fill for upcoming dates and bold text for today. In Manage Rules, review the order and scope of overlapping rules. Stop If True is available for relevant rule types, but it is not necessary for every set of rules.

If no cells change, check these common causes:

Wrong starting row: Make the formula row match the first row in Applies to.
Wrong date column: Update the column letter in the formula and preserve the dollar sign if the rule spans several columns.
Dates stored as text: Convert the values to real Excel dates, rather than merely applying a date display format.
Unexpected completed-status text: Check for different wording, trailing spaces, or other status labels.
Rule range or priority: Confirm Applies to and inspect other rules affecting the same cells.

TODAY() is a changing function, not a timestamp captured when the rule is made. The displayed result updates when Excel recalculates the workbook; if a workbook is set to manual calculation, a recalculation may be needed. Press F9 in Excel for Windows to recalculate. Conditional formatting changes the display, not the due-date value itself, and it does not send reminders or move tasks automatically.

Choosing a useful visual scheme

Color should explain urgency at a glance without making the worksheet harder to read. A practical scheme might use red for overdue, amber for the coming week, and a stronger border or bold font for due today. Keep text readable against the fill, and consider using an icon or a status label as well if color alone would be ambiguous to a reader.

Decide how to define “upcoming” before creating a rule. A seven-day window based on TODAY()+7 means calendar days, including weekends. For a project that works only on weekdays, a workday-based deadline is a different requirement and needs a business-calendar approach, including a decision about which holidays count. Do not label a calendar-day rule as “next five workdays” unless it actually accounts for weekends and applicable holidays.

Also decide how to handle completed tasks. If completed items should no longer appear urgent, incorporate the status column into the rule or archive completed tasks separately. If the status might be written as Complete, Done, or Closed, use a consistent data-entry choice; inconsistent labels are a common reason a completion exception does not behave as expected.

Frequently Asked Questions

What formula highlights overdue dates in Excel?

With due dates in B2:B100 and the first selected row at 2, use =AND(ISNUMBER($B2),$B2<TODAY()). It highlights valid date values earlier than today and leaves blanks and text unformatted.

How do I highlight dates due today?

With dates in column B beginning at B2, use =AND(ISNUMBER($B2),$B2=TODAY()). If the cells contain times as well as dates, use INT around the cell reference so times on today’s date still match.

Can conditional formatting highlight an entire row?

Yes. Select the table rows to format, such as A2:D100, then use a formula referencing the due-date column with a locked column and relative row, for example =AND(ISNUMBER($B2),$B2<TODAY()). The row number must match the first selected row.

Why does my due-date rule highlight blank cells?

A formula may be testing blanks as if they were dates, or another rule may be applying a format. Add an ISNUMBER check, such as =AND(ISNUMBER($B2),$B2<TODAY()), and inspect other rules and the Applies to range.

Does the formula update when the day changes?

Yes. TODAY() returns the current date when Excel recalculates, so the rule can change as time passes. It does not create a permanent record of when a task became overdue; use a stored date or status if you need that history.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.