Excel Guides

Conditionally Formatting for Multiple Date Comparisons in Excel

You can use conditional formatting in Excel to highlight cells based on multiple criteria. For example, you might want to highlight cells that are:

  • Due in the next 7 days
  • Overdue
  • Completed

To do this, you'll need to set up 3 separate conditional formatting rules. Here's how:

  1. Select the cells you want to format.
    For this example, we'll select cells A2:E9.
  2. Create your first rule.
    Click the Conditional Formatting button on the Home tab, then choose New Rule. In the New Formatting Rule dialog box, select Use a formula to determine which cells to format. In the "Format values where this formula is true:", enter the following formula: =AND($D2="No",A2-TODAY()<7)

  3. [Format cells]
    Click the [Format...]. In the [Format Cells][Fill] [OK]
  4. (Optional) Create your second rule.
    Repeat steps 2-4, but use this formula instead: =AND($D2="No",A2-TODAY()>7)

  5. (Optional) Create your third rule.
    Repeat steps 2-4, but use this formula instead: =$D2="Yes"

Move beyond 

Excel

Get started with Causal today.
Build models effortlessly, connect them directly to your data, and share them with interactive dashboards and beautiful visuals.