Conditional Formatting for Deadlines

A bill list or project sheet is easier to scan when overdue rows turn red and "due this week" turns amber. Do it with formula-based conditional formatting, not manual fills.

Sample table

A	B	C
Task	Due	Owner
Invoice ACME	2026-03-01	Sam
Tax prep	2026-08-31	Sam
Domain renew	2026-09-05	Lee

Dates must be real date values, not text. Test: Ctrl+1 → if you see Date format, good. Text dates break comparisons.

Rule 1 — Overdue (red)

Select A2:C100 (include all columns you want shaded).

Excel: Home → Conditional Formatting → New Rule → Use a formula to determine which cells to format

Formula:

=$B2<TODAY()

Format: red fill, white or dark text.

Google Sheets: Format → Conditional formatting → Custom formula is → same formula.

$B2 locks column B; row changes per row. < TODAY() means before today.

Rule 2 — Due today (bold border)

Add another rule, same range:

=$B2=TODAY()

Format: thick outline or yellow fill.

Rule 3 — Due in next 7 days

=AND($B2>=TODAY(), $B2<=TODAY()+7)

Format: light orange fill.

Rules apply top to bottom in Excel — put the most specific rules first if colors overlap. In Excel, use Stop if true on higher-priority rules when needed.

Keyboard shortcuts

Alt+H, L (Excel) opens Conditional Formatting menu
Ctrl+ (backtick) toggles formula view — check that B2 is a serial date number, not "2026-03-01"` text

Budget sheet tie-in

Use the same technique on a Due column next to fixed bills in monthly budget sheet.

Inventory reorder dates work the same way on a Next order by column in inventory tracker.

Wrong vs Right

Wrong	Right
`=B2<TODAY()` without `$` on column B — format only column A correctly	`=$B2<TODAY()`
Dates as text from CSV	Import as dates or `=DATEVALUE(B2)` then paste values
`=B2<TODAY()-1` for "overdue" — excludes yesterday edge cases awkwardly	`=$B2<TODAY()` for strictly before today

Protect formatted sheet

Lock formula columns after setup: protect formulas.