多字段IF/THEN条件格式设置：基于日期与状态的单元格背景着色需求

Got it, let's walk through how to build this multi-condition conditional formatting exactly as you need it. I’ll break it down step by step so it’s easy to follow:

First, let's align on what your threshold cells represent. Based on your setup:

• For Active status (B3/B4 = "Active"), use values in A10-A12 as your red/yellow/green day thresholds
• For Potential status (B3/B4 = "Potential"), use values in A15-A17 as your thresholds

I’ll assume your rules follow typical urgency logic (adjust symbols if your specific rules differ):

• Red: Days remaining ≤ lowest threshold (e.g., A10 for Active)
• Yellow: Days remaining between lowest and middle threshold (e.g., A10 < days ≤ A11 for Active)
• Green: Days remaining > middle threshold (e.g., days > A11 for Active)

All rules rely on comparing the date in A3/A4 to today’s date. Use this formula to get the number of future days left:

=A3-TODAY()

This gives a positive number if the date is in the future, negative if it’s past.

1. Select cell A3
2. Go to Home > Conditional Formatting > New Rule
3. Choose "Use a formula to determine which cells to format" for each rule below

Rule 1: Red Background (Active Status) #

• Formula:

  =AND(B3="Active", (A3-TODAY())<=$A$10)
  

• Click Format > Fill, pick red, then hit OK

Rule 2: Yellow Background (Active Status) #

• Formula:

  =AND(B3="Active", (A3-TODAY())>$A$10, (A3-TODAY())<=$A$11)
  

• Set fill color to yellow, then OK

Rule 3: Green Background (Active Status) #

• Formula:

  =AND(B3="Active", (A3-TODAY())>$A$11)
  

• Set fill color to green, then OK

Rule 4: Red Background (Potential Status) #

• Formula:

  =AND(B3="Potential", (A3-TODAY())<=$A$15)
  

• Set red fill, OK

Rule 5: Yellow Background (Potential Status) #

• Formula:

  =AND(B3="Potential", (A3-TODAY())>$A$15, (A3-TODAY())<=$A$16)
  

• Set yellow fill, OK

Rule 6: Green Background (Potential Status) #

• Formula:

  =AND(B3="Potential", (A3-TODAY())>$A$16)
  

• Set green fill, OK

Once A3’s rules are set up, you can quickly apply them to A4:

• Select A3
• Click the Format Painter icon (Home tab, clipboard section)
• Click A4 – all rules will copy over, with absolute references ($A$10, etc.) staying intact

• Make sure A3, A4, and your threshold cells (A10-A12, A15-A17) are formatted as Date/Number (not text) – otherwise the formulas won’t calculate correctly
• Adjust the comparison operators (<, >, <=, >=) in the formulas if your "首条规则" has different logic (e.g., if red is for days exceeding a threshold instead of being below it)

内容的提问来源于stack exchange，提问作者Adam Pace