Return Overdue if due date in C2 is before today and status in D2 is not Paid, otherwise OK

公式

=IF(AND(C2<TODAY(), D2<>"Paid"), "Overdue", "OK")

说明

A flag column is easier to filter and to feed into conditional formatting than a long nested visual rule. Keep the logic in one IF.

步骤

  1. AND requires both: past due and not paid.
  2. TRUE → Overdue, FALSE → OK.
  3. Point conditional formatting at this column if you want color.

变体

Traffic-light 0/1 for counting

Double unary turns TRUE/FALSE into 1/0 so you can SUM the flags.

=--AND(C2<TODAY(), D2<>"Paid")

Due in the next 7 days

Leaves paid or far-future rows blank.

=IF(AND(C2>=TODAY(), C2<=TODAY()+7, D2<>"Paid"), "Due soon", "")

Case-insensitive status

Treats PAID and paid the same.

=IF(AND(C2<TODAY(), LOWER(D2)<>"paid"), "Overdue", "OK")