Highlight Dates in Excel: Overdue, Due Today, Weekends

Part of the free Module 7: Conditional Formatting · Lesson 13 of 15 · Full Excel course

Updated on 5 September 2026 · Works in Excel 365, 2021, 2019 and 2016 unless noted.

To highlight dates in Excel with conditional formatting, select the date column, go to Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format, and enter a formula that compares each date with TODAY(). For example =$D2<TODAY() highlights overdue dates, =$D2=TODAY() highlights items due today, and =WEEKDAY($D2,2)>5 highlights weekends. The rule recalculates every day, so the highlights move on their own.

Table of Contents

Why formula rules beat the A Date Occurring preset

The preset Highlight Cells Rules > A Date Occurring covers ten fixed periods such as Yesterday, This week and Next month. It cannot express “overdue”, “due within 3 days”, “weekend” or “older than 90 days”, and its idea of a week always starts on Sunday. A formula rule can express any of those, because you write the comparison yourself using TODAY(), WEEKDAY() and simple arithmetic. Excel evaluates the formula for every cell in the Applies to range, treats a TRUE result (or any non-zero number) as “format this cell” and FALSE (or zero) as “leave it alone”.

The rules in this lesson all use the same sample layout: a task list in A1:E21 with Task in column A, Owner in column B, Status in column C, Due Date in column D and Notes in column E. The dates are real Excel dates, not text. If you are unsure, select column D and check that the values line up on the right; text lines up on the left.

How the cell reference in a date rule works

Every formula rule is written for the top-left cell of the Applies to range, and Excel copies it across the rest of the range exactly as if you had filled a formula down and across. That is why the dollar signs matter:

Reference	What is locked	Effect in a rule applied to A2:E21
`D2`	Nothing	Each cell tests the cell one row up and three columns left of itself. Only the date column itself works; other columns test the wrong cell.
`$D2`	Column D	Every cell in a row tests column D of its own row. Use this to highlight the whole row.
`D$2`	Row 2	Every cell in a column tests row 2 of its own column. Use this for a date header row across the top.
`$D$2`	Both	Every cell tests the single cell D2. Use this only for one input cell, such as a report date typed in a box.

One more habit saves a lot of confusion: after clicking in the rule’s formula box, press F2 before using the arrow keys. Without F2 the arrow keys insert cell references instead of moving the cursor.

Step by step: highlight overdue and due-today rows

Select A2:E21, starting the selection from cell A2 so that A2 is the active cell.
Go to Home > Conditional Formatting > New Rule.
Choose Use a formula to determine which cells to format.
In the formula box type =AND($D2<TODAY(),$C2<>"Done"). The first test finds dates before today; the second stops finished tasks from showing as overdue.
Click Format, choose a light red fill on the Fill tab, then click OK twice.
With the same range selected, repeat steps 2 and 3 and enter =$D2=TODAY(). Give it a yellow fill. Click OK twice.
Open Conditional Formatting > Manage Rules and check that both rules show =$A$2:$E$21 under Applies to.

Because both rules use $D2, every cell in a row looks at column D of that row, so the fill covers the whole row rather than only the date cell. If only the date cell changes colour, the selection was made without the dollar sign, or the range was selected from the wrong corner.

Date rule formulas you can copy

All formulas assume the date is in column D, the rule is applied from row 2, and the whole row should be highlighted. Remove the $ before D if you only want the date cell itself to change.

Highlight	Formula	Notes
Overdue (before today)	`=$D2<TODAY()`	Add `$D2<>""` inside AND to ignore blanks, which count as 0 and would otherwise be overdue.
Due today	`=$D2=TODAY()`	Fails if column D holds date-time values; wrap with `INT($D2)`.
Due in the next 7 days	`=AND($D2>TODAY(),$D2<=TODAY()+7)`	Change 7 to any number of days.
This week (Monday to Sunday)	`=AND($D2>=TODAY()-WEEKDAY(TODAY(),2)+1,$D2<=TODAY()-WEEKDAY(TODAY(),2)+7)`	Uses WEEKDAY type 2 so Monday is day 1.
Weekend dates	`=WEEKDAY($D2,2)>5`	Type 2 returns 6 for Saturday and 7 for Sunday.
Weekday dates	`=WEEKDAY($D2,2)<6`	Monday to Friday only.
This month	`=AND(MONTH($D2)=MONTH(TODAY()),YEAR($D2)=YEAR(TODAY()))`	Both tests are needed or September 2025 would match.
Older than 90 days	`=$D2<TODAY()-90`	Typical for ageing debtors or stale records.
Between two dates in cells	`=AND($D2>=$H$1,$D2<=$H$2)`	H1 and H2 hold the start and end dates; both are fully locked.
Blank date cell	`=$D2=""`	Useful as a grey “no date set” flag.

Understanding TODAY and WEEKDAY inside a rule

TODAY() returns the current date as a serial number with no time part. Excel stores dates as whole numbers (5 September 2026 is 46270), so comparing a date with TODAY(), or adding 7 to it, is plain arithmetic. The function is volatile: it recalculates whenever the workbook calculates, which is why the highlights update the next time you open the file. NOW() includes the time, so avoid it in date rules unless you wrap it in INT.

WEEKDAY(date, return_type) returns a number for the day of the week. With the default return type 1, Sunday is 1 and Saturday is 7, which makes a weekend test read OR(WEEKDAY($D2)=1,WEEKDAY($D2)=7). With return type 2, Monday is 1 and Sunday is 7, so a weekend is simply WEEKDAY($D2,2)>5. Use type 2 for anything based on a Monday-start week; it keeps the formulas short and readable.

Worked example: a task tracker with three date rules

Suppose today is 5 September 2026 and the tracker holds these rows:

Task	Owner	Status	Due Date
Send invoice	Amit	Open	02-Sep-2026
Board pack	Priya	Open	05-Sep-2026
Stock count	Rahul	Done	01-Sep-2026
Site visit	Neha	Open	06-Sep-2026
Audit reply	Amit	Open	10-Sep-2026

Apply three rules to A2:D6, all with the formula rule type and A2 as the active cell:

Red fill: =AND($D2<TODAY(),$C2<>"Done")
Yellow fill: =$D2=TODAY()
Grey font: =WEEKDAY($D2,2)>5

Result: the Send invoice row turns red because 2 September is past and the task is still open. The Board pack row turns yellow because it is due today. Stock count stays plain even though its date is past, because Status is Done. Site visit gets grey text because 6 September 2026 is a Sunday. Audit reply is untouched. Open the file tomorrow and Board pack moves from yellow to red without any edit to the rules.

Tips and common mistakes

Text dates never match. Dates imported from a CSV or typed with a trailing space are text. Convert them with Data > Text to Columns, finishing with the Date option, or the DATEVALUE function.
Blank cells count as overdue. An empty cell equals 0, which is 0 January 1900, so $D2<TODAY() is TRUE. Add $D2<>"" inside AND.
Select from the top-left corner. If you drag from E21 up to A2, the active cell is E21 and the formula is offset by 19 rows. Check the Name Box before writing the rule.
Do not type the date into the formula as text. =$D2<"05/09/2026" compares a number with text. Use DATE(2026,9,5) or a cell reference.
Time stamps break equality tests. 05-Sep-2026 09:30 is not equal to TODAY(). Use INT($D2)=TODAY().
Put the most urgent rule at the top. If a task is both overdue and due today, the higher rule in Manage Rules wins the fill colour. The rule precedence lesson explains the order.
Keep the Applies to range tight. A whole-column range such as D:D makes Excel evaluate a million rows every recalculation.

Errors and how to fix them

Symptom	Cause	Fix
Nothing is highlighted	Dates are stored as text	Convert with Text to Columns or DATEVALUE; real dates align right
Only the date cell changes, not the row	Column not locked	Change `D2` to `$D2` in the rule
Wrong rows are highlighted	Active cell was not the top-left cell when the rule was written	Edit the rule so its row matches the first row of Applies to
Every empty row is red	Blank equals 0, which is before today	Use `=AND($D2<>"",$D2<TODAY())`
Highlights do not update the next day	Calculation set to Manual	Press F9 or set Formulas > Calculation Options > Automatic
Saturday is not treated as weekend	WEEKDAY default type used with a type-2 test	Use `WEEKDAY($D2,2)>5` consistently

Practice exercise

Open the course practice file or build a 20-row task list with a Due Date column, then:

Highlight overdue rows in red, ignoring rows whose Status is Done.
Highlight rows due in the next 3 days in orange, using TODAY()+3.
Give weekend due dates a grey font using WEEKDAY with return type 2.
Put a start date in H1 and an end date in H2, and highlight rows that fall between them with fully locked references.
Reorder the rules in Manage Rules so overdue always wins over the 3-day warning, and confirm the result on a row that qualifies for both.

Key takeaways

Formula rules with TODAY() highlight overdue, due today and upcoming dates, and the highlights roll forward every day.
WEEKDAY(date,2) makes weekend tests simple: a result above 5 is Saturday or Sunday.
Lock the column with $D2 to colour whole rows; leave it relative to colour only the date cell.
Write the rule for the top-left cell of the Applies to range; Excel shifts it for every other cell.
Blank cells and text dates are the two most common reasons a date rule misfires.
Combine tests with AND and OR, and order rules so the most urgent colour is applied first.

Related lessons

Frequently asked questions

How do I highlight dates that are past due in Excel?

Select the range, choose New Rule > Use a formula to determine which cells to format, and enter =$D2<TODAY() where D is the date column and 2 is the first row of the selection. Pick a red fill. To ignore empty cells, use =AND($D2<>"",$D2<TODAY()).

How do I highlight the whole row when a date is due today?

Select all the columns of the table, not only the date column, with the top-left cell active. Use the formula =$D2=TODAY(). The dollar sign before D locks the column so every cell in the row reads the date in column D of its own row, which is what makes the entire row change colour.

Can conditional formatting highlight weekends automatically?

Yes. Use =WEEKDAY($D2,2)>5. WEEKDAY with return type 2 numbers Monday as 1 and Sunday as 7, so any value above 5 is Saturday or Sunday. For a Friday and Saturday weekend, use =OR(WEEKDAY($D2,2)=5,WEEKDAY($D2,2)=6).

Why does my date rule highlight blank cells?

Excel treats an empty cell as zero, and zero as a date is 0 January 1900, which is before any real date. A test such as $D2<TODAY() is therefore TRUE for blanks. Wrap the test in AND with $D2<>"" so blanks are excluded.

Do date rules update when the day changes?

Yes. TODAY() is recalculated every time the workbook calculates, including when you open the file, so the highlights move forward without editing the rules. If they seem stuck, press F9 or check that Formulas > Calculation Options is set to Automatic.

Want the finished version? Ready-made Excel task trackers and KPI dashboards with due-date highlighting built in are available at NextGenTemplates.com.

← PreviousExcel Conditional Formatting Rules Manager: Manage and Clear All lessonsConditional Formatting Next →Conditional Formatting Based on Another Cell, Drop-Down or Checkbox