How to Highlight Overdue Tasks Automatically

Select the rows, add a conditional formatting rule with this formula, and anything past its date turns red on its own.

=AND($C2<>"", $C2<TODAY())

The $C2<TODAY() half is the obvious part. The $C2<>"" half is what stops every empty row below your data going red too — and leaving it out is why most people's first attempt looks broken.

Nobody set any of these colours. Note the due date on row 5 sitting to the left of its cell while every other date sits right — that is the whole difference.

Why blank rows turn red

Excel stores dates as numbers counted from 1 January 1900. An empty cell reads as zero in a comparison, and zero on that scale is the last day of 1899 — comfortably before today.

So =$C2<TODAY() is perfectly true for every blank cell in the column, and Excel colours all of them. The rule is working; the question was just badly asked.

AND fixes it by requiring both things: there is a date, and it has passed.

Three tiers, in the right order

One colour tells you what's late. Three tell you what to do.

Order	Formula	Fill
1	=$D2="Done"	Grey
2	=AND($C2<>"", $C2<TODAY())	Red
3	=AND($C2<>"", $C2>=TODAY(), $C2<=TODAY()+7)	Amber

Done comes first, with Stop If True ticked. A finished task that was delivered late is still finished, and colouring it red buries the things you can still act on.

Rule order is set in Conditional Formatting → Manage Rules, evaluated top to bottom. Most people never open that dialog, which is why overlapping rules so often produce colours nobody chose.

⚠️ Every formula uses $C2 — column locked, row free. That's what lets one date column colour the whole row. Without the $, the test slides sideways as Excel works across the columns, and rows colour in fragments.

Setting it up

1. Select the data rows and all the columns you want coloured. Not the header.
2. Home → Conditional Formatting → New Rule → Use a formula.
3. Write the formula for the first cell of your selection. Data starting on row 5 needs $C5, not $C2.
4. Set the fill, then repeat for each tier.
5. Open Manage Rules and drag them into order, ticking Stop If True on the Done rule.

Calendar days or working days

TODAY()+7 counts seven calendar days, weekends included. If your "due soon" window is really five working days:

=AND($C2<>"", $C2>=TODAY(), $C2<=WORKDAY(TODAY(), 5))

WORKDAY skips Saturdays and Sundays. Add a range of public holidays as a third argument and it skips those too. WORKDAY.INTL handles weekends that aren't Saturday and Sunday — relevant if your working week runs Sunday to Thursday.

Better still, put the window in a cell and reference it, so a change from 7 to 14 is one edit rather than a rewrite of the rule.

The failure you won't see

Look at the fourth row in the example above. Its due date passed five days ago, the Days left column reports that correctly, and it isn't coloured at all.

The date is stored as text.

What makes this so hard to spot is that the two things behave differently:

• Arithmetic converts text. ="2026-08-30"-TODAY() gives a real number, so the Days column looks fine.
• Comparison doesn't. Excel ranks all text as greater than all numbers, so "2026-08-30" < TODAY() is FALSE — always, for any text date, however old.

A text date can therefore never be overdue as far as conditional formatting is concerned. No error, no warning, just a row that never colours.

Spotting and fixing it

Dates that are really dates sit to the right of the cell. Text drifts left. That's the fastest tell, and it survives any number format.

To be certain, put =ISNUMBER(C2) beside the column. FALSE is a text date.

To fix a whole column, select it and use Data → Text to Columns → Finish. It sounds like it does nothing and in fact re-parses every cell in place. Where that fails — usually a format Excel doesn't recognise, like 30/08/2026 in a US locale — DATEVALUE in a helper column is the fallback.

TODAY() changes on its own

That's the point, and it has consequences worth knowing.

TODAY() re-evaluates whenever the file opens and on most edits, so the colours move without anyone touching them. A sheet reviewed on Friday shows different rows on Monday, which is exactly what you want from a tracker.

Two caveats. It reads the local machine's clock, so a colleague in another timezone can briefly see a different answer around midnight. And a file printed or exported to PDF freezes whatever the colours were at that moment — fine, as long as nobody treats the PDF as current a fortnight later.

Does this work in Google Sheets?

Yes. Format → Conditional formatting → Custom formula is, with the same formulas. Rule order is set by dragging in the side panel rather than in a Manage Rules dialog, and Stop If True is implicit — the first matching rule wins.

TODAY, AND and WORKDAY behave identically.

When it goes wrong

Every empty row is red

The $C2<>"" guard is missing. Blank reads as zero, and zero is 1899.

One row refuses to colour

Its date is text. Check whether it sits left or right in the cell.

Only part of each row colours

Missing $ before the column letter.

The colours are a row or two out

The formula was written for a different starting row than the selection began on. Delete and redo it.

Manage Rules shows forty rules and I made three

This one catches everyone eventually. Copying and pasting rows splits conditional formatting into fragments, each covering a few rows, and they multiply every time. Delete the lot with Clear Rules → Clear Rules from Entire Sheet and reapply the three rules once to the whole range. Copy with Ctrl+Shift+V (values only) afterwards to stop it recurring.

Done rows are still red

The Done rule is below the overdue rule, or Stop If True isn't ticked.

Common questions

Can I colour by how late something is?

Yes — add tiers with wider tests, most severe first: more than 30 days late, then more than 7, then any. Order is everything, since a task 40 days late satisfies all three.

Can I highlight dates in the past week rather than the future?

Swap the comparison: =AND($C2<>"", $C2>=TODAY()-7, $C2<=TODAY()). Useful for "what happened recently" rather than "what's coming".

Why not use the built-in "A Date Occurring" rule?

It's fine for a single column and offers Yesterday, Last Week, Next Month and so on. It can't colour a whole row, can't combine with a status column, and has no blank guard. Once you need any of those, you need a formula.

Can I sort or filter by colour?

Yes — right-click a coloured cell, Filter → Filter by Selected Cell's Color. Worth knowing, though a real status column is more reliable to work with than a colour.

Should the sheet email me instead?

Not from Excel without VBA or Power Automate. Colour is what a spreadsheet does well; if you genuinely need to be told rather than to look, that's a job for something else.

The short version

• =AND($C2<>"", $C2<TODAY()) — the guard matters as much as the comparison.
• Blank cells read as zero, and zero is 1899. That's why they go red.
• Lock the column, leave the row free, to colour whole rows.
• Put the Done rule first with Stop If True.
• WORKDAY instead of +7 when the window is in working days.
• A date stored as text will never highlight — arithmetic converts it, comparison doesn't.
• Forty rules where you made three means someone copied rows. Clear and reapply.

Next steps: this rule is what drives the invoice tracker and, in its progress-against-time form, the project tracker. The nested-IF ordering that decides which colour wins is covered here, and the same mixed anchoring appears in highlighting duplicates.