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

Highlight Weekends and Holidays in a Date Column

Shade weekend and public-holiday rows in an Excel log or timeline with openpyxl conditional formatting: WEEKDAY-based rules for Saturdays and Sundays, a holiday list generated with the holidays package on its own sheet, rule order so holidays win, the same rules across a date header row for planners, and a recalculated check of which dates each rule catches.
Conditional formattingDates & times

Daily logs, timesheets, delivery schedules and project timelines all have a date column, and the first thing a reader looks for is which days were not normal working days. Shading weekends and public holidays makes gaps and spikes explain themselves — no orders on 31 August, because it was a bank holiday. Doing it with conditional formatting rather than fixed fills means the shading stays correct when rows are added, dates are edited or the sheet is re-sorted. This page writes the rules with openpyxl: a WEEKDAY test for weekends, a holiday list generated with the holidays package and kept on its own sheet, an ordering that lets holidays take precedence, the same rules applied across a row of dates for planner layouts, and a check of which dates each rule actually catches. It belongs to Applying Conditional Formatting with openpyxl.

Prerequisites

Bash
pip install openpyxl holidays

The holidays package knows the public holidays of most countries and their regions. The examples use England's bank holidays for 2026 and a daily orders log from 24 August to 13 September 2026, which spans the late-summer bank holiday. LibreOffice (soffice) recalculates helper formulas in Step 5; it is optional.

Step 1 — Write the data and the holiday list

Generate the holidays for the years the sheet covers and put them on their own sheet, with names, so readers can see why a date is shaded:

Python
from datetime import date, timedelta
import holidays
from openpyxl import Workbook
from openpyxl.styles import Font

wb = Workbook()
log = wb.active
log.title = "Orders"
log.append(["Date", "Orders", "Revenue"])
start = date(2026, 8, 24)
for i in range(21):
    d = start + timedelta(days=i)
    busy = d.weekday() < 5 and d != date(2026, 8, 31)
    log.append([d, 40 + (i * 7) % 25 if busy else 3, (40 + (i * 7) % 25) * 52.5 if busy else 120.0])
for row in log.iter_rows(min_row=2, max_col=1):
    row[0].number_format = "ddd dd mmm yyyy"
log.column_dimensions["A"].width = 18

hol = wb.create_sheet("Holidays")
hol.append(["Date", "Holiday"])
for d, name in sorted(holidays.UK(subdiv="ENG", years=[2026, 2027]).items()):
    hol.append([d, name])
    hol.cell(hol.max_row, 1).number_format = "yyyy-mm-dd"
for ws in (log, hol):
    for c in ws[1]:
        c.font = Font(bold=True)
print(hol.max_row - 1, "holidays listed; first:", hol["B2"].value)

holidays.UK(subdiv="ENG", years=[...]) returns England's bank holidays — including substitute days such as "Boxing Day (observed)" when a holiday falls on a weekend — as a dictionary of dates and names. Writing them as real dates on a Holidays sheet, two years at a time, keeps the rule simple and lets someone add a company shutdown day by hand. The number format ddd dd mmm yyyy shows the weekday next to each date in the log, which helps readers check the shading. Use your own country and region codes; the package covers well over a hundred countries.

Step 2 — A weekend rule

WEEKDAY(date, 2) numbers Monday as 1 and Sunday as 7, so a weekend is any value above 5. Apply the rule to the whole data range so entire rows are shaded:

Python
from openpyxl.formatting.rule import FormulaRule
from openpyxl.styles import PatternFill

last = log.max_row
weekend_fill = PatternFill("solid", fgColor="E7E6E6")
holiday_fill = PatternFill("solid", fgColor="FCE4D6")

weekend_rule = FormulaRule(formula=["WEEKDAY($A2,2)>5"], fill=weekend_fill, font=Font(color="7F7F7F"))
print("weekend rule:", weekend_rule.formula)

The second argument of WEEKDAY matters: with the default (1), Sunday is 1 and Saturday is 7, so >5 would wrongly shade Thursdays and Fridays. Return type 2 makes the test read naturally. $A2 anchors the column, so every cell in the row looks at the row's date, and leaves the row relative, so each row tests its own date. The rule is created here and added in the next step, together with the holiday rule, because their order matters.

Step 3 — A holiday rule that takes precedence

A holiday that falls on a weekday should look different from an ordinary weekend, and a holiday on a weekend should still look like a holiday. Add the holiday rule first and stop evaluation there:

Python
holiday_rule = FormulaRule(formula=["COUNTIF(Holidays!$A$2:$A$200,$A2)>0"], fill=holiday_fill,
                           font=Font(color="C65911", bold=True), stopIfTrue=True)
log.conditional_formatting.add(f"A2:C{last}", holiday_rule)
log.conditional_formatting.add(f"A2:C{last}", weekend_rule)
wb.save("orders_shaded.xlsx")
print([r.formula for rules in log.conditional_formatting._cf_rules.values() for r in rules])

Rules added first get the higher priority. With stopIfTrue, a date that is a holiday gets the holiday format and the weekend rule is not applied, so a bank holiday Monday is orange and bold rather than grey. The COUNTIF compares each date with the holiday list; because both are real dates, the comparison is exact — if the dates in either place were text, nothing would match. The list range is larger than the list, so holidays for later years can be appended without editing the rule. Rule order is covered in more detail in Control Conditional Formatting Rule Order and Stop If True.

Which format a date gets Each date is first checked against the holiday list. If it is a holiday, it gets the holiday format and evaluation stops. Otherwise, if WEEKDAY with return type 2 is above 5, it gets the weekend format. Any other date keeps the normal format. For each date in column A in the holiday list holiday format orange, bold · stop here WEEKDAY(date,2) > 5 weekend format grey otherwise normal row The shaded orders log Rows for Friday 28 August to Tuesday 1 September. Saturday 29 and Sunday 30 August are shaded grey as weekends. Monday 31 August is shaded orange as the late summer bank holiday, with only three orders. A B C 1 Date Orders Revenue 2 Fri 28 Aug 2026 43 2,257.50 3 Sat 29 Aug 2026 3 120.00 4 Sun 30 Aug 2026 3 120.00 5 Mon 31 Aug 2026 3 120.00 6 Tue 01 Sep 2026 46 2,415.00

Step 4 — The same rules across a row of dates

Planners and Gantt-style sheets put dates across the top and people or tasks down the side. The rules are the same, with the anchoring turned round: fix the row of the date header and let the column move:

Python
plan = wb.create_sheet("Plan")
plan["A1"] = "Person"
for i in range(21):
    plan.cell(1, 2 + i, start + timedelta(days=i)).number_format = "dd"
for r, person in enumerate(["Ana", "Ben", "Chloé"], start=2):
    plan.cell(r, 1, person)
last_col = plan.cell(1, 22).column_letter
plan.conditional_formatting.add(f"B1:{last_col}4", FormulaRule(
    formula=["COUNTIF(Holidays!$A$2:$A$200,B$1)>0"], fill=holiday_fill, stopIfTrue=True))
plan.conditional_formatting.add(f"B1:{last_col}4", FormulaRule(formula=["WEEKDAY(B$1,2)>5"], fill=weekend_fill))
for col in range(2, 23):
    plan.column_dimensions[plan.cell(1, col).column_letter].width = 4
wb.save("orders_shaded.xlsx")
print("plan rules on", f"B1:{last_col}4")

B$1 fixes row 1 and leaves the column relative, so every cell in a column tests that column's date — whole columns are shaded for weekends and the holiday, from the header down through every person's row. It is the same idea as Step 2, transposed. The narrow columns with a dd format make a compact timeline; the Create a Gantt Chart in Excel with Python page builds a fuller version.

Anchoring for a date column versus a date row When dates run down column A, the rule uses $A2 so every cell in a row checks that row's date. When dates run across row 1, the rule uses B$1 so every cell in a column checks that column's date. dates down a column rule: WEEKDAY($A2,2)>5 column fixed, row relative whole rows shaded dates across a row rule: WEEKDAY(B$1,2)>5 row fixed, column relative whole columns shaded

Step 5 — Check which dates each rule catches

Python cannot see conditional formatting, but it can recalculate the rules' formulas in helper cells and compare the result with the calendar and the holiday list:

Python
import subprocess
from openpyxl import load_workbook

chk = load_workbook("orders_shaded.xlsx")
ws = chk["Orders"]
ws["E1"], ws["F1"] = "Holiday?", "Weekend?"
for r in range(2, last + 1):
    ws[f"E{r}"] = f"=COUNTIF(Holidays!$A$2:$A$200,$A{r})>0"
    ws[f"F{r}"] = f"=WEEKDAY($A{r},2)>5"
chk.save("orders_check.xlsx")
subprocess.run(["soffice", "--headless", "--convert-to", "xlsx", "--outdir", "calc", "orders_check.xlsx"],
               check=True, capture_output=True)
calc = load_workbook("calc/orders_check.xlsx", data_only=True)["Orders"]
uk = holidays.UK(subdiv="ENG", years=2026)
for r in range(2, last + 1):
    d = calc[f"A{r}"].value.date()
    shown = "holiday" if calc[f"E{r}"].value else "weekend" if calc[f"F{r}"].value else ""
    expected = "holiday" if d in uk else "weekend" if d.weekday() >= 5 else ""
    if shown or expected:
        print(d.strftime("%a %d %b"), shown, "✓" if shown == expected else f"✗ expected {expected}")

Every Saturday and Sunday in the three weeks is caught by the weekend rule, and Monday 31 August by the holiday rule, matching Python's own calendar and the holidays package. Note that d.weekday() >= 5 is Python's numbering (Monday = 0), while Excel's WEEKDAY(..., 2) > 5 uses Monday = 1 — two different conventions that agree on which days are weekends, which is exactly what this check confirms. Running it on a copy keeps the delivered workbook free of helper columns.

Common pitfalls

  • WEEKDAY without the second argument. The default numbering starts on Sunday; use return type 2.
  • Dates stored as text. Neither WEEKDAY nor COUNTIF against real dates will work.
  • Weekend rule before the holiday rule. A weekday holiday then looks ordinary; put holidays first with stop-if-true.
  • Holiday lists for one year only. Generate several years and leave room in the rule's range.
  • Wrong anchoring for date headers. Use B$1 when dates run across a row.

Conclusion

To shade weekends and holidays, add two formula rules to the date range: a holiday rule first — COUNTIF against a holiday sheet generated with the holidays package, with stop-if-true — and a weekend rule WEEKDAY($A2,2)>5 after it. For dates across a header row, the same rules use B$1 instead. Keep the dates real dates on both sheets, leave room in the holiday range for future years, and check the rules by recalculating their formulas in helper cells against Python's own calendar.

Frequently asked questions

How do I highlight weekends in Excel with openpyxl? Add a FormulaRule with WEEKDAY($A2,2)>5 to the data range. Return type 2 numbers Monday as 1 and Sunday as 7, so values above 5 are Saturday and Sunday.

How do I highlight public holidays in Excel with Python? Write the holiday dates to a sheet, for example from the holidays package, and add a FormulaRule with COUNTIF of that range against the row's date greater than 0.

Why does my weekend rule highlight Thursdays and Fridays? WEEKDAY without a second argument numbers Sunday as 1 and Saturday as 7, so the test for values above 5 picks Thursday and Friday. Use WEEKDAY(date,2).

How do I make holidays take priority over weekends? Add the holiday rule before the weekend rule and set stopIfTrue on it, so dates matching the holiday rule do not get the weekend format.

More on conditional formatting

More on conditional formatting →