Excel中基于日期对比实现单元格条件着色的技术问询

API 探路者

2026-5-15

Got it, let's sort out this conditional formatting for your Manufacturing ETA Excel file—it’s totally doable with a few quick steps. Here’s exactly how to set it up to match your rules:

Step 1: Select your target column (Column F)

Click the column header for F to select the entire column, or just highlight the specific cells with your updated dates (no need to include empty rows if you don’t want to).

Step 2: Open the Conditional Formatting tool

Head to the Home tab in Excel’s ribbon.
Find the Conditional Formatting button, click it, then pick New Rule from the dropdown.

Step 3: Create the green fill rule (F is earlier than E)

In the New Formatting Rule window, select "Use a formula to determine which cells to format"—this is the most flexible option for your needs.
In the formula input box, type:
=$F1<$E1
- Pro tip: If your data starts at row 2 (like row 1 is a header), swap the 1 for a 2 (=$F2<$E2). The dollar signs lock the column letters, so Excel checks the right E/F pair for every row.
Click Format, go to the Fill tab, pick your preferred green shade, then hit OK twice to save the rule.

Step 4: Create the red fill rule (F is later than E)

Go back to Conditional Formatting > Manage Rules.
Click New Rule again, and select the same formula-based option as before.
Enter this formula:
=$F1>$E1
- Again, adjust the row number if your data starts after row 1.
Click Format, choose your red color from the Fill tab, then click OK twice.

Step 5: Double-check the rule order (critical!)

In the Conditional Formatting Rules Manager, make sure your two new rules are at the top of the list. Excel applies rules in order, so these should take priority over any existing rules you might have.
Leave the "Stop if true" box unchecked for both rules—since a cell can’t be both earlier and later than E at the same time, there’s no conflict here.

Quick test tip

To make sure it works, type a date in F that’s earlier than the corresponding E date—you’ll see the green fill show up right away. A later date will turn red, and if F matches E, nothing happens (perfect for your requirements).
If you ever need to tweak the colors or formulas, just go back to Manage Rules, select the rule, hit Edit Rule, and make your changes.

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