Excel 按日期做條件格式（逾期、即將到期和週末）

Tested in: The date comparisons and WEEKDAY results below were evaluated in Google Sheets on 12 November 2026. Excel 365 menu names are the current desktop labels; I did not click through Conditional Formatting in Excel this session.

The general feature guide is in Conditional Formatting in Excel. If a formula rule highlights the wrong rows, see formula not working. This page is only about date rules.

Overdue against a cutoff

Pick a cutoff you can test. In the test workbook the cutoff was 1 March 2024:

=A2<DATE(2024,3,1)

or, with the cutoff in F1:

=A2<$F$1

On 12 November 2026, =DATE(2024,2,20)<DATE(2024,3,1) was TRUE, and the same comparison for 1 March and for 4 March was FALSE. A due date that equals the cutoff is not overdue under a strict < rule; use <= if the due date itself should count.

Apply the rule to A2:A100 (or however long the list is), not to A1 if A1 is a header. To color the entire row, apply it to A2:F100 and lock the date column: =$A2<$F$1.

TODAY() is fine when you want the cutoff to move: =A2<TODAY(). I did not evaluate TODAY() in the test workbook on a frozen date, because it would change tomorrow. For a screenshot or a test file, prefer DATE or a cell.

Upcoming dates, and weekends

A window from the cutoff through the next seven days:

=AND(A2>=$F$1,A2<=$F$1+7)

If F1 is 1 March 2024, 4 March is inside that window and 20 February is not.

For weekends, use WEEKDAY with return type 2 so Monday is 1 and Sunday is 7:

=OR(WEEKDAY(A2,2)=6,WEEKDAY(A2,2)=7)

In the test workbook, =WEEKDAY(DATE(2024,3,4),2) returned 1 (4 March 2024 was a Monday) and =WEEKDAY(DATE(2024,3,9),2) returned 6 (a Saturday). Type 1 of WEEKDAY is different (Sunday = 1); do not mix the two types in the same rule.

Google Sheets uses the same formulas in Format > Conditional formatting > Custom formula is.

When the rule does nothing

If =ISNUMBER(A2) is FALSE, the cell is text that looks like a date, and a comparison against DATE or TODAY() will not highlight it reliably. Convert the column first, as in dates stored as text.

A rule written as =A1<$F$1 on a range that starts at A2 is aimed one row too high. A cutoff typed as 2024-03-01 but stored as text is the same class of problem as text dates in the list.

Built-in rules such as A Date Occurring > Yesterday can work for quick filters, but a formula you can test in a helper column is easier to check. Put =A2<$F$1 next to the first date, fill down, and confirm the TRUE/FALSE pattern before you rely on the color.