The core formula for highlighting overdue dates in Excel is =A2<TODAY() applied to a range starting at A2, which flags any date before today. For a more complete rule, use =AND(A2<>"",A2<TODAY()) to ignore blank cells, and combine with <=TODAY()+7 for upcoming deadlines.
Why Conditional Formatting for Due Dates Matters
Manual date tracking fails the moment a spreadsheet grows past a few rows. Conditional formatting shifts the work to Excel: it scans every date in a range and applies a visual style—red fill, bold text, an icon—when the date meets your rule. This is especially useful for project timelines, invoice payment terms, subscription renewals, or any list where a missed deadline has a cost. Unlike a static color you paint once, conditional formatting recalculates every time the sheet opens or changes, so tomorrow’s overdue items highlight themselves without you touching a cell.
How the Due Date Formula Works
Excel stores dates as serial numbers (1 = January 1, 1900), so comparing dates is simple arithmetic. TODAY() returns the current date’s serial number, recalculating on each worksheet change or file open. The formula =A2<TODAY() returns TRUE when the date in A2 is earlier than today, which triggers the format. For due dates that fall on today itself, use <= instead of <. The formula must be written relative to the first cell in your selected range—if your dates start in B5, the formula references B5, not A2.
Here is a concrete example. Assume column A holds due dates from A2 to A20, and you want to highlight overdue items in red. Select A2:A20, go to Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format, enter =AND(A2<>"",A2<TODAY()), click Format, choose a red fill, and press OK. The AND wrapper prevents blank cells from being treated as zero (which is less than today), so empty rows stay unformatted.
How to Choose the Right Rule for Your Deadline
One formula rarely fits every scenario. Match the rule to the meaning of “due” in your context:
- Overdue only:
=A2<TODAY()– highlights past dates. - Due today or overdue:
=A2<=TODAY()– catches today’s deadlines. - Due within 7 days:
=AND(A2>=TODAY(),A2<=TODAY()+7)– flags upcoming items, often in yellow. - Overdue by more than 30 days:
=AND(A2<TODAY(),A2<=TODAY()-30)– separates critical from recent lapses. - Ignore completed tasks: If column B holds a status like “Done,” add
$B2<>"Done"to the condition, e.g.,=AND($A2<TODAY(),$B2<>"Done").
When mixing multiple rules on the same range, order matters. Excel applies the first TRUE rule and stops, so place the most specific rule (e.g., overdue by 30 days) above the general overdue rule. Use Manage Rules to reorder them.
Common Problems and How to Fix Them
Three issues trip up most users. First, the formula highlights the wrong cells because the selection anchor is off. The formula must reference the top-left cell of your selected range; if you select A2:A20 but write =B2<TODAY(), Excel checks column B for every row, which is usually not what you want. Second, dates stored as text—like “2025-03-01” typed with an apostrophe—do not compare numerically. Use =ISNUMBER(A2) to test; if FALSE, convert text to dates with DATEVALUE or re-enter them. Third, the rule stops working after you insert rows. Always define the range with a table (Insert > Table) or use a dynamic range like $A$2:$A$1000 so new rows inherit the formatting.
Comparing Due Date Formulas with Other Methods
Conditional formatting is not the only way to visualize deadlines. A data bar (Home > Conditional Formatting > Data Bars) shows the relative length of time until each due date, but it does not distinguish overdue from upcoming. An icon set can show a red X for overdue and a checkmark for done, but it requires a formula-based rule to map the icons. A pivot table can count overdue items, but it needs manual refresh and does not color individual cells. The formula-based conditional formatting remains the most flexible because it combines any logical test with any cell format—fill, font, border, or icon.
Safety and Accuracy Tips for Date Formulas
Because TODAY() is volatile, it recalculates whenever the workbook recalculates, which can be surprising if you expect a snapshot. If you need a fixed “as of” date, replace TODAY() with a cell reference like $D$1 containing a static date. Also, beware of regional date formats: Excel interprets dates according to your system locale, so a formula that works with mm/dd/yyyy may misbehave with dd/mm/yyyy. Always verify by entering a known past date and checking if the rule fires. Finally, before applying any rule to a large dataset, test it on a small copy—conditional formatting is easy to remove (Home > Conditional Formatting > Clear Rules) but can be confusing to debug if applied broadly.
Frequently Asked Questions
How do I highlight a row based on a due date in one column? Select the entire row range (e.g., A2:D20) and use a formula with a mixed reference like =$A2<TODAY(). The dollar sign locks the column, so Excel checks column A for each row but formats the whole row.
Can I use conditional formatting to highlight dates between two dates? Yes. Use =AND(A2>=DATE(2025,1,1),A2<=DATE(2025,12,31)) to highlight all dates in 2025. Replace the DATE functions with cell references for dynamic ranges.
Why is my formula not working when the cell contains a date from a formula? If the date is returned by a formula like =TODAY()+7, it is still a real date, so conditional formatting should work. If it appears as text, check that the formula cell is formatted as a date, not Text.
Does conditional formatting slow down large spreadsheets? Yes, especially with volatile functions like TODAY() across thousands of cells. To improve performance, limit the range to the actual data (e.g., A2:A1000 instead of entire columns) and avoid using whole-column references.
How do I remove conditional formatting from a range? Select the range, go to Home > Conditional Formatting > Clear Rules > Clear Rules from Selected Cells. To clear all rules in the sheet, choose Clear Rules from Entire Sheet.
Related
- Java String Built-In Methods: Uses, Examples, and Tips
- Java String Reverse: Methods and Examples
- String in Java: Meaning, Methods, and Examples
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Formula Budget Calculator: How to Build OneTo turn Excel into a budget calculator, use a simple formula like =SUM(B2:B13) to total your income and…
- Excel Let Cell Overflow? How to Show Text Across CellsYes. In Excel for Microsoft 365 on Windows, text can overflow into the cells to its right when…
- Conditional Formatting in Excel: If-Then Color RulesYes. In Excel, select the cells you want to format, then choose Home > Conditional Formatting and create…
- Excel FILTER Function: Multiple Criteria and ExamplesYes. In Microsoft Excel for Microsoft 365, use the FILTER function with multiplication (*) to require multiple conditions…
- Excel Formula for Counting Calendar Days: The Easy WayThe simplest Excel formula for counting calendar days between two dates is =B2-A2 (where B2 is the later…
- Power Query Fuzzy Match: How It Works and How to Use ItYes. Power Query can fuzzy-match text when you merge queries, helping connect values that are similar but not…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.