Yes. Excel conditional formatting can use a nested IF formula, but the formula must ultimately return TRUE or FALSE to decide whether a format applies. For most date rules, separate conditional-formatting rules are clearer; use nested IF when the date test genuinely depends on another value, such as a task’s priority.
How conditional formatting evaluates date formulas
Conditional formatting checks a formula for each cell in the selected range. When the formula evaluates to TRUE, Excel applies the chosen format; when it evaluates to FALSE, it does not. A nested IF can make that decision, but it does not directly select a color or return a label such as “Overdue.” Choose the format in the rule’s formatting options, and use the formula to determine which cells qualify.
Excel stores valid dates as serial numbers, so comparisons such as earlier than, equal to, or later than work as numeric comparisons. The date displayed in a cell may look different because of its number format, but formatting does not change the underlying date value. A date typed as text, however, may not behave like a real Excel date.
Conditional formatting rules can also use formulas that contain IF, AND, OR, and other worksheet functions. In Microsoft 365 for Windows, choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Enter the formula, choose a format, and confirm the rule. On Mac, the same feature is under Home > Conditional Formatting > New Rule, although the dialog can look different. Excel for the web supports conditional formatting, but the desktop app is often more convenient for managing complex rules.
When a nested IF is useful
A nested IF is helpful when one condition changes what date threshold to test. For example, suppose column B contains due dates and column C contains a priority label. You might want urgent tasks to be highlighted if due within three days, normal tasks if due within seven days, and other priorities not highlighted. Select B2:B100 and create a formula-based rule using this formula:
With due dates in B2:B100 and priority labels in C2:C100, and B2:B100 selected, use =IF(OR($B2="",NOT(ISNUMBER($B2))),FALSE,IF($C2="Urgent",$B2<=TODAY()+3,IF($C2="Normal",$B2<=TODAY()+7,FALSE))).
The outer IF prevents blank cells and non-date entries from being treated as due dates. The inner IF checks the priority: “Urgent” uses a three-day window, “Normal” uses seven days, and anything else returns FALSE. Assign a fill color to the rule. The formula moves down one row for each cell in the selected range because the row number is relative; the dollar signs keep the date and priority columns fixed.
This example includes dates already overdue, since a past date is also less than either future threshold. If you mean “due soon but not overdue,” add a lower boundary to the date test. Depending on the intended logic, that can be easier to understand as separate rules or a formula using AND rather than more nested IF layers.
Set up straightforward date rules
For common tasks such as highlighting overdue, due today, and upcoming dates, separate rules are usually the easiest option to read and maintain. Assume due dates are in B2:B100. Select that range, then create one formula-based rule for each condition. These formulas ignore blanks and can use different formats:
- For overdue dates, with B2:B100 selected and dates in column B, use
=AND($B2<>"",$B2<TODAY()). - For dates due today, with the same selection and layout, use
=AND($B2<>"",$B2=TODAY()). - For dates from tomorrow through seven days from today, with the same selection and layout, use
=AND($B2>TODAY(),$B2<=TODAY()+7).
Choose a different format for each rule, such as red for overdue, amber for due today, and green for upcoming. The third test assumes that B contains date-only values. If it contains date-and-time values, a time later today is greater than TODAY(), so it could be included in that “upcoming” window. Decide whether that is appropriate for the workbook before using the rule.
To shade an entire task row based on its due date, select the rows’ full data area, for example A2:D100, rather than just B2:B100. With dates in column B and the selected range beginning in row 2, use =AND($B2<>"",$B2<TODAY()). The dollar sign locks the date column, while the row number stays relative, so each row’s format responds to its own date.
Choose between one nested rule and several rules
One nested formula can be compact, but compact does not always mean easy to maintain. With several independent conditions, separate rules make the formatting and logic visible: a reader can inspect the overdue rule without unpacking a chain of IF statements. Separate rules also let you give each condition its own color.
A single nested rule is more suitable when conditions share one format and the test for one case depends on a preceding choice, as in the priority example. Keep the order of the IF tests deliberate: Excel checks them from left to right and returns the result associated with the first condition it finds true. If categories overlap, put the most specific or highest-priority case first.
For multiple formats, make separate rules even if you use nested IF logic elsewhere. Conditional formatting does not interpret text returned by an IF formula as an instruction to choose a fill color. For instance, returning “Red” or “Green” is not a substitute for creating rules and assigning formats. A formula-based rule is a test, not a formatting script.
Reference style and rule management
Cell references determine how a rule adjusts as Excel applies it across a range. If the range starts at B2, a reference such as B2 adjusts by row and column. A reference such as $B2 fixes the column but lets the row adjust, which is useful for highlighting a whole row according to its date in column B. A reference such as B$2 fixes the row, while $B$2 fixes both.
Check the “Applies to” range as well as the formula. Open Home > Conditional Formatting > Manage Rules to review existing rules and their ranges. If a rule is intended for B2:B100 but applies to a different range, its relative references may be evaluated from an unexpected starting cell.
When two rules apply to the same cell, Excel may apply aspects of both formats. In the Rules Manager, rule order and the Stop If True setting can affect which rules continue to be evaluated. Use Stop If True only when the rule’s position and behavior match your intended priority; it is not a general fix for every overlap. On Mac, open the conditional-formatting rules manager from the Home tab; its layout may differ from Windows.
Handle blanks, timestamps, and date boundaries
Blank cells deserve an explicit check in many date rules. Depending on the comparison and cell contents, omitting that check can produce results that look surprising, especially in ranges with incomplete records. A guard such as $B2<>”” prevents an empty cell from qualifying in common date tests. If imported data might contain text, ISNUMBER can add another check because valid Excel dates are stored numerically.
Timestamps are another frequent source of confusion. A cell that displays only a date may actually contain a time as well. Equality with TODAY() compares the underlying values, so a timestamp at 3 p.m. today is not equal to midnight at the start of today. If the goal is to treat any time today as today, compare the integer date portion instead:
With date-and-time values in B2:B100, B2:B100 selected, and the goal of highlighting all entries from today, use =AND($B2<>"",ISNUMBER($B2),INT($B2)=TODAY()).
INT removes the fractional time portion from a numeric date-time value. For date-only data, the simpler equality rule is preferable. Also decide how to treat the boundary: “within seven days” can mean through the same date seven days from now, or it can exclude that final date. Use < or <= accordingly and test a date on the boundary.
Common problems and practical checks
- No cells highlight: Check that the formula returns TRUE for a sample row, that the selected range starts on the row referenced in the formula, and that the date is a real Excel date rather than text.
- The wrong row changes: Review absolute and relative references. A rule applied starting at row 2 usually needs row references based on row 2, not row 1 or row 3.
- Blank rows get formatted: Add a blank check, and consider ISNUMBER if the range contains imported text or error-prone entries.
- Today’s entries are missed: Check whether the cells include times. A date-time value will not equal TODAY() unless its time is midnight.
- Colors seem inconsistent: Open Manage Rules, inspect the Applies to range, and check for overlapping rules or unexpected rule order.
- The formula is rejected: Confirm balanced parentheses and straight quotation marks. This article’s formulas use comma separators; some regional Excel settings use different list separators.
TODAY() updates when Excel recalculates the workbook, so a “due today” highlight changes as the date changes. That makes it useful for live schedules, but it is not a permanent record of what was due on the day a workbook was last opened. For a historical snapshot, store a fixed date value rather than relying on a moving TODAY() comparison.
Frequently Asked Questions
Can conditional formatting use a nested IF formula?
Yes. Use a formula-based conditional formatting rule, and make sure the final result is TRUE or FALSE. Nested IF is useful when an earlier choice, such as priority, determines which date test should be used.
Can one IF formula assign different colors?
No. The formula determines whether a rule applies; the rule’s formatting settings specify its appearance. Create separate conditional-formatting rules when different date conditions need different colors.
Why does Excel highlight blank date cells?
A date comparison without a blank check can behave unexpectedly in an incomplete range. Add a condition that the date cell is not blank, and check that the rule’s Applies to range and starting row are correct.
How do I highlight a whole row when a date is overdue?
Select the full row range, such as A2:D100, and use a formula that locks the date column but leaves the row relative. For dates in column B, a typical test is =AND($B2<>"",$B2<TODAY()).
Why does a date with a time not match TODAY()?
TODAY() represents the current date at midnight, while a date-time cell can contain a later time. Compare the integer date portion with TODAY() if every timestamp on that calendar date should count as today.
Does conditional formatting update when the date changes?
Rules that use TODAY() update when Excel recalculates. If the workbook is set to manual calculation, it may not refresh until recalculation; press F9 on Windows to recalculate, or use the corresponding calculation command on Mac.
Related
- Convert H:M:S to a Number in Excel
- Contact Number List Template for Excel: Setup Guide
- Contacts List Template Excel: Build a Useful Workbook
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Won’t Calculate Formulas? Causes, Checks, and FixesIf Excel does not calculate a formula, first check Formulas > Calculation Options and select Automatic. If that…
- Excel Protect Sheet From Being Deleted: The Right SettingTo stop a worksheet from being deleted in Excel for Microsoft 365 on Windows, protect the workbook’s structure:…
- Filter Excel Table by Text: Steps, Options, and FixesTo filter an Excel table by text in Microsoft 365 for Windows, select the drop-down arrow in the…
- VLOOKUP Between Two Sheets in Excel: A Practical GuideTo look up a value from one Excel sheet on another, enter a VLOOKUP formula such as =VLOOKUP(A2,Products!$A$2:$C$100,3,FALSE).…
- Excel IF ISNUMBER: Check Whether a Cell Contains a NumberTo make Excel return one result when a cell contains a number and another when it does not,…
- SUMIF Excel Criteria: Syntax, Examples, and Common FixesIn Excel, SUMIF criteria tell the function which cells to check before adding matching values. For example, with…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.