1 00:00:00,080 --> 00:00:04,340 A simple due-date rule often colors empty cells because Excel treats 2 00:00:04,340 --> 00:00:06,115 them differently from real dates. 3 00:00:06,115 --> 00:00:09,311 It may also flag work that is already done. 4 00:00:09,311 --> 00:00:13,216 This fictional task board needs exactly two highlighted rows: tasks with 5 00:00:13,216 --> 00:00:17,476 a real due date before today and a status other than Done. 6 00:00:17,476 --> 00:00:21,381 We will paste relative dates, build one formula rule, and compare 7 00:00:21,381 --> 00:00:23,867 every row against an independent answer check. 8 00:00:24,467 --> 00:00:26,189 You have two practice paths. 9 00:00:26,189 --> 00:00:30,321 Paste the tab-separated task board into A one of a disposable 10 00:00:30,321 --> 00:00:34,109 blank worksheet to build the rule yourself, or open the supplied 11 00:00:34,109 --> 00:00:36,520 Excel workbook to inspect the finished rule. 12 00:00:36,520 --> 00:00:40,653 Both use TODAY-relative dates: one due date is truly blank, one 13 00:00:40,653 --> 00:00:44,096 overdue task is Done, and one task is due today. 14 00:00:44,096 --> 00:00:47,885 Confirm that the formulas evaluate as dates before testing the highlight. 15 00:00:48,485 --> 00:00:50,499 Read all six tasks before formatting. 16 00:00:50,499 --> 00:00:53,520 Send draft is two days late and still Open. 17 00:00:53,520 --> 00:00:56,541 Fix invoice is one day late and In progress. 18 00:00:56,541 --> 00:00:59,562 Those are the only rows that should turn red. 19 00:00:59,562 --> 00:01:03,591 Budget review is in the future, Sign-off has no due date, 20 00:01:03,591 --> 00:01:06,947 Archive is already Done, and Publish notes is due today. 21 00:01:06,947 --> 00:01:11,311 These boundary cases reveal a wrong less-than sign or missing blank guard. 22 00:01:11,911 --> 00:01:15,019 Select A two through C seven, excluding the header. 23 00:01:15,019 --> 00:01:19,507 In Excel for the web, Microsoft documents Home, Styles, Conditional Formatting, New Rule. 24 00:01:19,507 --> 00:01:23,305 Confirm the Apply to range is the six data rows, then 25 00:01:23,305 --> 00:01:25,377 choose Formula as the rule type. 26 00:01:25,377 --> 00:01:29,174 The first cell in the selected range is row two, so 27 00:01:29,174 --> 00:01:31,246 the formula must reference row two. 28 00:01:31,246 --> 00:01:35,044 If you start at a different row, adjust that row number. 29 00:01:35,644 --> 00:01:39,855 Enter the guide's AND formula using ISNUMBER of dollar B two, 30 00:01:39,855 --> 00:01:44,767 dollar B two less than TODAY, and dollar C two not equal to Done. 31 00:01:44,767 --> 00:01:48,627 ISNUMBER excludes the blank date; strict less-than excludes today; the 32 00:01:48,627 --> 00:01:50,381 status check excludes completed work. 33 00:01:50,381 --> 00:01:53,890 Lock columns B and C, but let row numbers move. 34 00:01:53,890 --> 00:01:57,750 Excel may display the saved relative references as R C two 35 00:01:57,750 --> 00:01:59,153 and R C three. 36 00:01:59,753 --> 00:02:03,189 Choose a readable light red fill and complete the rule. 37 00:02:03,189 --> 00:02:07,654 Microsoft says the Formula rule evaluates TRUE or FALSE for the selected cells. 38 00:02:07,654 --> 00:02:11,433 Inspect the Apply to range again; if it covers only one 39 00:02:11,433 --> 00:02:14,525 due-date cell, you will not highlight whole tasks. 40 00:02:14,525 --> 00:02:18,304 If it includes the header, move the range back to A 41 00:02:18,304 --> 00:02:19,678 two through C seven. 42 00:02:19,678 --> 00:02:23,113 Appearance alone is not enough until the row count matches. 43 00:02:23,713 --> 00:02:27,599 The final board should highlight only Send draft and Fix invoice. 44 00:02:27,599 --> 00:02:32,545 Now change Fix invoice from In progress to Done; its red fill should disappear. 45 00:02:32,545 --> 00:02:37,492 Change it back and clear its due date; it should also lose the fill. 46 00:02:37,492 --> 00:02:39,258 Restore the original values afterward. 47 00:02:39,258 --> 00:02:43,144 These two small tests confirm both guards react to edits instead 48 00:02:43,144 --> 00:02:45,264 of merely matching the starting screenshot. 49 00:02:45,864 --> 00:02:48,215 If blank dates turn red, check ISNUMBER. 50 00:02:48,215 --> 00:02:51,909 If no row turns red, confirm the TODAY formulas are dates 51 00:02:51,909 --> 00:02:54,932 and the rule covers A two through C seven. 52 00:02:54,932 --> 00:02:58,627 In English Excel for the web, the prebuilt fictional workbook loaded 53 00:02:58,627 --> 00:03:01,985 one rule and highlighted only Send draft and Fix invoice. 54 00:03:01,985 --> 00:03:05,680 Creating a new rule and testing temporary edits in the live 55 00:03:05,680 --> 00:03:08,031 app remain checks for your own copy.