Python Excel AutomationPractical guides for developers
How-to
Advanced Data Transformation And CleaningDeep dive

Highlight Overdue Dates in Excel with openpyxl

Mark overdue and due-soon rows in an Excel tracker with conditional formatting that uses TODAY(), so the colours stay correct every day the file is opened — ignore blank and completed rows, order rules with stopIfTrue, add a days-overdue column and avoid the text-date trap.
Conditional formattingDates & timesopenpyxl

A task tracker, an invoice list or a contract register needs one thing at a glance: what is late. Colouring the late rows red in Python at the moment the report is generated works for a day. Open the file next week and the colours are wrong — last Friday's "due soon" is now overdue, and nothing shows it. A conditional formatting rule with TODAY() recalculates each time the workbook is opened, so the colours are always right. This guide, part of Applying Conditional Formatting with openpyxl, builds overdue and due-soon rules for a whole-row tracker and handles the blanks, completed items and text dates that break naive rules.

A task tracker with overdue and due-soon rows Six tasks. Renew licence is ten days overdue and open, so its row is red. Send invoices is past due but done, so it is not highlighted. Quarter close is due in three days and Board pack is due today, so both rows are amber. Audit prep is weeks away, and Update budget has no due date, so neither is highlighted. A B C D 1 Task Owner Due Status 2 Renew licence Ann today − 10 Open 3 Send invoices Bob today − 2 Done 4 Quarter close Cai today + 3 Open 5 Audit prep Dee today + 20 Open 6 Update budget Eve Open 7 Board pack Fay today Open red is overdue and open; amber is due within seven days

Prerequisites

Bash
pip install openpyxl

The tracker, with due dates relative to the day the script runs so the example always shows the same pattern:

Python
import datetime as dt

from openpyxl import Workbook, load_workbook
from openpyxl.formatting.rule import FormulaRule
from openpyxl.styles import Font, PatternFill

today = dt.date.today()
wb = Workbook()
ws = wb.active
ws.title = "Tasks"
ws.append(["Task", "Owner", "Due", "Status"])
for task, owner, days, status in [("Renew licence", "Ann", -10, "Open"), ("Send invoices", "Bob", -2, "Done"),
                                  ("Quarter close", "Cai", 3, "Open"), ("Audit prep", "Dee", 20, "Open"),
                                  ("Update budget", "Eve", None, "Open"), ("Board pack", "Fay", 0, "Open")]:
    ws.append([task, owner, today + dt.timedelta(days=days) if days is not None else None, status])
for cell in ws["C"][1:]:
    cell.number_format = "yyyy-mm-dd"
ws.column_dimensions["A"].width = 16
ws.column_dimensions["C"].width = 12
last = ws.max_row

Step 1 — The overdue rule

A formula rule is written for the top-left cell of the range and applied relative to every other cell. Lock the column with $ and leave the row free, so every cell in a row looks at that row's due date and status:

Python
RED_FILL = PatternFill("solid", fgColor="FCE9E9")
RED_FONT = Font(color="B91C1C", bold=True)

overdue = FormulaRule(
    formula=['AND($C2<>"", $C2<TODAY(), $D2<>"Done")'],
    fill=RED_FILL, font=RED_FONT, stopIfTrue=True,
)
ws.conditional_formatting.add(f"A2:D{last}", overdue)

Three conditions, each for a reason. $C2<TODAY() is the overdue test. $C2<>"" stops blank due dates from counting — Excel treats an empty cell as zero, which is a day in January 1900 and therefore long overdue. $D2<>"Done" leaves completed items alone, so Bob's invoices, finished but past their date, stay plain. The same whole-row technique is covered in general in Highlight an Entire Row Based on a Cell Value with openpyxl.

Step 2 — Due-soon as a second rule

A second rule marks open items due today or within the next seven days. stopIfTrue on the overdue rule means a row that is overdue is not also considered for the amber rule:

Python
AMBER_FILL = PatternFill("solid", fgColor="FDEFD8")
due_soon = FormulaRule(
    formula=['AND($C2<>"", $C2>=TODAY(), $C2<=TODAY()+7, $D2<>"Done")'],
    fill=AMBER_FILL,
)
ws.conditional_formatting.add(f"A2:D{last}", due_soon)
wb.save("tasks.xlsx")

Rules added first get the higher priority. Because the two conditions here do not overlap — before today, and from today onwards — the order only matters if you later widen one of them, but setting stopIfTrue on the most urgent rule is a good habit. Opening the saved file in LibreOffice on the day it was written shows Renew licence in red, Quarter close and Board pack in amber, and the other rows plain.

How each row is classified A row with no due date or with status Done is left plain. Otherwise, a due date before today makes the row red and stops further rules. A due date from today up to seven days ahead makes it amber. Anything later stays plain. What does the row's due date say? blank or Done plain no rule applies before today red stopIfTrue within 7 days amber includes today later plain not urgent yet

Step 3 — A days-overdue column

Colour tells people something is late; a number tells them how late, and it can be sorted and filtered. Add a formula column that shows the days overdue for open items and stays blank otherwise:

Python
ws["E1"] = "Days overdue"
for row in range(2, last + 1):
    ws[f"E{row}"] = f'=IF(AND(C{row}<>"", C{row}<TODAY(), D{row}<>"Done"), TODAY()-C{row}, "")'
    ws[f"E{row}"].number_format = "0"
wb.save("tasks.xlsx")
print(ws["E2"].value)

The column uses the same three conditions as the rule, so the colour and the number never disagree, and like the rule it recalculates whenever the workbook is opened. The colour rules still cover only columns A to D; Step 5 extends them. Writing formulas is covered in Write a Formula to an Excel Cell with openpyxl.

Step 4 — Make sure the dates are dates

The most common reason an overdue rule "does not work" is that the due column holds text. A string such as "2026-09-13" never compares as less than TODAY(), so the row stays plain. Check the types before writing, and convert anything that is not a date:

Python
def as_date(value):
    if isinstance(value, dt.datetime):
        return value.date()
    if isinstance(value, dt.date) or value is None:
        return value
    return dt.date.fromisoformat(str(value).strip())

wb = load_workbook("tasks.xlsx")
ws = wb["Tasks"]
ws["C3"] = "2026-01-15"                       # someone typed a date as text
text_dates = [c.coordinate for c in ws["C"][1:] if isinstance(c.value, str)]
print(text_dates)                             # ['C3']
for cell in ws["C"][1:]:
    cell.value = as_date(cell.value)
print(type(ws["C3"].value).__name__)          # date

When the data comes from pandas, convert the column with pd.to_datetime before writing; see Parse Excel Dates into Python datetimes with pandas.

Step 5 — Keep the rule range growing with the data

A rule on A2:D7 does not cover row 8 when someone adds a task. Two approaches keep new rows covered: apply the rule to a generous range such as A2:D1000 — the blank-date condition keeps empty rows plain — or format the data as an Excel table and apply the rule to whole columns of it. The generous range is simpler and works everywhere:

Python
from openpyxl.formatting.formatting import ConditionalFormattingList

ws.conditional_formatting = ConditionalFormattingList()     # drop the earlier, narrower rules
ws.conditional_formatting.add("A2:E1000", overdue)
ws.conditional_formatting.add("A2:E1000", due_soon)
wb.save("tasks.xlsx")
print([(str(cf.sqref), len(cf.rules)) for cf in ws.conditional_formatting])   # [('A2:E1000', 2)]

Replacing the sheet's rule list before adding the new ones matters. Adding a wider rule on top of the old narrow ones leaves both in the file, and every run of a script that edits an existing workbook adds another copy. Starting from an empty list — or removing just the rules you own — keeps Excel's rule manager readable.

Checking and cleaning up rules that have accumulated on a sheet is covered in List and Remove Conditional Formatting with openpyxl.

Colour computed in Python versus a TODAY() rule A fill set by Python when the report runs is correct only on that day and goes stale as dates pass. A conditional formatting rule using TODAY() recalculates whenever the workbook is opened, so overdue rows are always current, and it also covers rows added later if the range is generous. Fill set in Python works in any viewer correct only on the run date new rows are never coloured fine for a frozen snapshot TODAY() rule correct every day it is opened covers rows added later one rule, no per-cell styling needs a viewer that calculates

Step 6 — Check the colours without opening Excel

A rule that looks right in code can still be wrong in a cell reference. On a server without Excel, render the sheet with LibreOffice and look at the picture — the quickest honest test of what a reader will see:

Python
import subprocess

subprocess.run(["soffice", "--headless", "--convert-to", "png", "tasks.xlsx"],
               check=True, capture_output=True, timeout=120)
print("wrote tasks.png")

LibreOffice evaluates TODAY() and the conditional formats when it renders, so the PNG shows exactly which rows are red and amber today. Run it as part of testing the report job, and keep the image with the test output so a broken rule is visible in review rather than discovered by a reader.

Common pitfalls

  • Relative references in the wrong place. C2 without a $ shifts to D2, E2 across the row, so only column C gets the right answer. Lock the column: $C2.
  • Status spellings. "Done", "done " and "Completed" are three different values to the rule. Standardise the status column or use a data validation list.
  • Time zones and midnight. TODAY() uses the clock of the machine opening the file. A team spread across time zones can see a task turn overdue at different moments; for deadlines that must be exact, compare timestamps rather than dates.
  • Viewers that do not calculate. Some previewers show the cached formatting only. The rule is still correct once opened in Excel or LibreOffice.

Conclusion

Overdue highlighting belongs in a conditional formatting rule, not in a fill set by Python, because TODAY() keeps it correct every day. Write the rule for the first data row with the column locked, exclude blank dates and completed items, give the most urgent rule stopIfTrue, add a days-overdue formula that uses the same conditions, make sure the due column holds real dates, and apply the rule to a range that leaves room for new rows.

Frequently asked questions

How do I highlight dates before today in Excel with openpyxl? Add a FormulaRule whose formula compares the date with TODAY(), such as $C2<TODAY(), to the range you want coloured. Because Excel recalculates TODAY() whenever the file is opened, the highlighting stays correct on later days.

Why are blank due dates highlighted as overdue? Excel treats an empty cell as zero, which is a date in 1900 and therefore before today. Add $C2<>"" to the rule with AND so blank dates are ignored.

Should I compute overdue status in Python instead? Only if the file is a snapshot. A colour computed in Python is frozen at the moment the script ran, while a TODAY() rule updates every day the workbook is opened.

Why does the rule not highlight some dates? Those cells probably hold text that looks like a date. Text never compares as less than TODAY(). Convert the column to real dates before writing it.

More on conditional formatting

More on conditional formatting →