Dates, Time & Schedules
Conditional Formatting for Dates: Blank-Safe Overdue, Due in 7 Days & Valid Date Checks
Prevent blank cells from lighting up red as "1900-01-00 Overdue" by pairing ISNUMBER($B2) with an explicit reference date $E$2 to classify Overdue, Due Within 7 Days, and Scheduled items.
Why =$B2 < TODAY() turns every blank row red—and how ISNUMBER($B2) fixes it!
In spreadsheet date arithmetic, an empty cell evaluates to numeric 0 (January 0, 1900), which is strictly less than any modern date serial! Writing a naive conditional-formatting rule =$B2 < $E$2 causes every blank template row at the bottom of your tracker to light up bright red as "Overdue"—while imported text dates ("2026/13/01") evaluate greater than all numbers and silently evade overdue alerts!
Guarding every date-formatting rule with ISNUMBER($B2) (and checking closed status $C2 <> "Completed") eliminates false alarms on empty rows and flags malformed text dates separately: Overdue is =AND(ISNUMBER($B2), $C2 <> "Completed", $B2 < $E$2), while Upcoming 7-Day Window is =AND(ISNUMBER($B2), $C2 <> "Completed", $B2 >= $E$2, $B2 <= $E$2 + 7)!
| Due Date Cell $B2 (Ref Date $E$2 = 2026-05-10) | Naive =$B2 < $E$2 | Blank-Safe =AND(ISNUMBER($B2), $C2<>"Completed", $B2<$E$2) |
|---|---|---|
| 2026-05-04 (Open) | TRUE (Red) | TRUE (Genuine Overdue SLA) |
| Blank Cell "" (Unscheduled Row) | TRUE (False Red Alarm!) | FALSE (Ignored Cleanly) |
| 2026-05-14 (Open, +4 days) | FALSE | FALSE (Triggers 7-Day Upcoming Rule Instead!) |
Blank-Safe SLA Date Tier Classifier (Overdue / Due <= 7d / On Track / Unscheduled)
Classifies numeric serial dates in B2 against reference date $E$2 while handling Completed items and blank/non-numeric cells.
=IF(TRIM($C2) = "Completed", "Completed", IF(NOT(ISNUMBER($B2)), "Unscheduled or Text Date", IF($B2 < $E$2, "Overdue", IF($B2 <= $E$2 + 7, "Due Within 7 Days", "Scheduled (> 7 Days)"))))Upcoming 7-Day Window Boolean Rule =AND(ISNUMBER($B2), TRIM($C2)<>"Completed", $B2>=$E$2, $B2<=$E$2+7)
Exact boolean formula for Amber/Yellow "Due Within 7 Days" conditional formatting.
=AND(ISNUMBER($B2), TRIM($C2) <> "Completed", $B2 >= $E$2, $B2 <= $E$2 + 7)Try the example
Use the original values or change them to test your understanding.