Conditional formatting for overdue and upcoming dates
Use a fixed reference date to test overdue and next-seven-days rules, including today and empty cells.
Download the example workbook (.xlsx)
Illustrative data. English function names and comma argument separators are shown; Excel may use different separators or translated function names. The file was structurally inspected, but native Excel calculation and UI behavior need local verification.
A repeatable date example
The workbook uses 21 September 2026 in C2 as a fixed reference date. Due dates are true Excel dates in B2:B5; B6 is blank. D2:D6 tests overdue and E2:E6 tests the next seven days.
=AND(ISNUMBER($B2),$B2<$C$2)
Apply this to A2:E6 for overdue rows. The only overdue task is row 2, due 20 September. Today is not overdue. For today through the next seven days, use:
=AND(ISNUMBER($B2),$B2>=$C$2,$B2<=$C$2+7)
Rows 3 and 4 qualify (21 and 28 September); row 5 (29 September) and blank row 6 do not. The workbook applies both rules to A2:E6: overdue rows use peach and upcoming rows use green. They cannot overlap for these conditions. The workbook keeps the reference date fixed so the expected answers remain reproducible.
Make it live later
When you want a rolling rule, replace $C$2 with TODAY(). Results will change by day and are no longer the fixed results shown above. Verify imported dates are actual date serials before using the rule; text that looks like a date can fail numeric comparisons. See text-to-date conversion.
Compatibility and source
Compatibility: The formulas and rules use desktop Excel’s conditional formatting or Data Validation controls. Excel for web and Mac may present different menus or rule editors. Check each rule after opening the file in your edition; native dialog behavior has not been tested here.
Formula and UI behavior are described in Microsoft’s Excel guidance. Start with the broad formula and shortcut reference or browse more Excel tips.