Excel + Google Sheets · Conditional formatting
Make late tasks turn red
A conditional formatting rule paints every overdue task red, every day, by itself.
The formula
=AND($C2<>"Done", $B2<TODAY())What each part means
$C2<>"Done"The task isn't finished$B2<TODAY()And the due date has passedAND(...)Both must be true
Example
TODAY: NOV 10
Home → Conditional formatting → New rule → Use a formula
| A | B | C | |
|---|---|---|---|
| 1 | Task | Due | Status |
| 2 | Send invoice | Nov 3 | Pending |
| 3 | Team report | Nov 14 | Pending |
| 4 | Pay supplier | Nov 7 | Done |
| 5 | Client call | Nov 8 | Pending |
| 6 | Budget Q1 | Nov 20 | Pending |
| A | B | C | |
|---|---|---|---|
| 1 | Task | Due | Status |
| 2 | Send invoice | Nov 3 | Pending |
| 3 | Team report | Nov 14 | Pending |
| 4 | Pay supplier | Nov 7 | Done |
| 5 | Client call | Nov 8 | Pending |
| 6 | Budget Q1 | Nov 20 | Pending |
Step by step
- Select the data, from row 2 down.
- Go to Home → Conditional formatting → New rule → Use a formula.
- Paste the formula:
=AND($C2<>"Done", $B2<TODAY()) - Click Format, pick a fill colour and press OK.
In Google Sheets: Format → Conditional formatting → Custom formula is.
Where it works
- Every version of Excel
- Google Sheets
- Excel for the web (free)
The same formula in other languages
- English Conditional formatting
- Español Formato condicional
- Português Formatação condicional