Build an Employee Leave Balance Report

Leave balances are a small calculation that generates a lot of questions: "how many days do I have left?", "why does my balance differ from my own count?", "who still has fifteen days to take before December?". HR systems hold the answer in principle, but the report people actually use is often a spreadsheet maintained by hand — and the hand-maintained version is where the errors live: a part-timer's Monday bank holiday counted as leave, a booking over New Year counted entirely in one year, a starter given a full year's entitlement. This page builds the report with pandas from two exports — the employee list and the leave bookings — counting every booking in working days according to each person's work pattern and the public holiday calendar, pro-rating entitlement for starters and part-timers, applying carry-over that expires, and flagging balances that need attention. It belongs to Building Finance and Operations Reports.

Prerequisites

pip install pandas numpy openpyxl holidays

The inputs are an employee list — start and end dates, the days of the week each person works, full-time entitlement and days carried over from last year — and a bookings export with one row per booking: employee, first and last day, and a half-day flag. The leave year is the calendar year 2026 and the report is run on 2 October 2026. Public holidays come from the holidays package for England; use your own calendar if your organisation's differs.

Step 1 — Count bookings in each person's working days

A booking from Monday to Friday is five days for a full-timer, but only three for someone who works Monday to Wednesday, and four if the Monday is a public holiday. NumPy's busday_count does this counting with a weekmask for the work pattern and a list of holidays to skip:

import holidays
import numpy as np
import pandas as pd

YEAR = 2026
YEAR_START, YEAR_END = np.datetime64(f"{YEAR}-01-01"), np.datetime64(f"{YEAR + 1}-01-01")
BANK_HOLIDAYS = np.array(sorted(holidays.country_holidays("GB", subdiv="ENG", years=[YEAR, YEAR + 1])), dtype="datetime64[D]")

emp = pd.read_excel("employees.xlsx")
book = pd.read_excel("leave_bookings.xlsx").merge(emp[["Employee", "Work days"]], on="Employee")

def days_in_year(row):
    start = max(np.datetime64(row["From"].date()), YEAR_START)
    end = min(np.datetime64(row["To"].date()) + np.timedelta64(1, "D"), YEAR_END)   # busday_count excludes the end
    if start >= end:
        return 0.0
    days = np.busday_count(start, end, weekmask=row["Work days"], holidays=BANK_HOLIDAYS)
    return 0.5 if row["Half day"] and days == 1 else float(days)

book["Days"] = book.apply(days_in_year, axis=1)
book["Taken"] = np.where(book["To"] < pd.Timestamp("2026-10-02"), "taken", "booked")
print(book[["Employee", "From", "To", "Work days", "Days", "Taken"]].head(8).to_string(index=False))

Clipping each booking to the leave year handles bookings that span the year end: Amira's leave from 21 December to 4 January counts only its 2026 working days this year — Christmas Day and the substitute Boxing Day holiday on Monday 28 December are skipped, so it costs her 7 days in 2026 rather than 9 — and the rest falls into next year's report. Chloe's booking from 30 March to 8 April would cost a full-timer 6 days, with Good Friday and Easter Monday skipped; she works Monday to Wednesday, so it costs her 5. busday_count treats its end date as exclusive, hence the extra day added to it. A half-day booking counts 0.5. Bookings whose last day is before the report date are taken; the rest are booked.

Step 2 — Pro-rata entitlement and carry-over

Entitlement depends on how much of the year someone is employed and how many days a week they work. Carried-over days often expire part-way through the year if not used:

CARRY_EXPIRY = pd.Timestamp(f"{YEAR}-03-31")

def entitlement(row):
    start = max(row["Start date"], pd.Timestamp(f"{YEAR}-01-01"))
    end = min(row["End date"] if pd.notna(row["End date"]) else pd.Timestamp(f"{YEAR}-12-31"), pd.Timestamp(f"{YEAR}-12-31"))
    year_fraction = ((end - start).days + 1) / 365
    fte = len(row["Work days"].split()) / 5
    return round(row["Full-time entitlement"] * fte * year_fraction * 2) / 2      # to the nearest half day

emp["Entitlement"] = emp.apply(entitlement, axis=1)
used_by_expiry = (book[book["From"] <= CARRY_EXPIRY].groupby("Employee")["Days"].sum()
                  .reindex(emp["Employee"], fill_value=0).values)
emp["Carry-over used"] = np.minimum(emp["Carried over"], used_by_expiry)
emp["Carry-over lost"] = emp["Carried over"] - emp["Carry-over used"]
print(emp[["Name", "Work days", "Entitlement", "Carried over", "Carry-over used", "Carry-over lost"]].to_string(index=False))

Ben started on 15 June, so he gets 200 days' worth of a 25-day year — 13.5 days rounded to the nearest half day. Chloe works three days a week, so her 25-day full-time entitlement becomes 15. Elena left on 31 August, so her entitlement is cut to the part of the year she worked, which matters when her final pay settles any untaken or overtaken leave. Carried-over days count only if used by 31 March; Farid used 5 days in January and keeps all 4 carried-over days, while Dev took his first leave in March and used all 5. Your own rounding and carry-over rules may differ; keep them in these two small functions where they are easy to find and change.

Step 3 — Balances and flags

days = book.pivot_table(index="Employee", columns="Taken", values="Days", aggfunc="sum", fill_value=0)
report = emp.set_index("Employee").join(days).fillna({"taken": 0, "booked": 0})
report["Available"] = report["Entitlement"] + report["Carry-over used"]
report["Remaining"] = report["Available"] - report["taken"] - report["booked"]
left_in_year = np.busday_count(np.datetime64("2026-10-02"), YEAR_END, holidays=BANK_HOLIDAYS)

def flag(r):
    if r["Remaining"] < 0:
        return "over-booked"
    if pd.isna(r["End date"]) and r["Remaining"] >= 10:
        return f"{r['Remaining']:g} days unbooked with {left_in_year} working days left"
    return ""

report["Flag"] = report.apply(flag, axis=1)
cols = ["Name", "Team", "Available", "taken", "booked", "Remaining", "Flag"]
print(report[cols].to_string())

Remaining balance is entitlement plus usable carry-over, less leave taken and leave already booked for later in the year. Two kinds of balance need someone's attention. A negative balance means more leave was taken or booked than allowed — Elena, who left at the end of August having taken more than her pro-rated entitlement, is the classic case, and payroll needs to know before her final pay. Farid is the other case: his December booking was approved although it took him six days past his allowance, which suggests bookings are approved without the balance in front of the approver — a process point the report makes visible. A large unbooked balance late in the year means either a rush of leave in December or days that will be lost, both of which managers would rather know about now.

Dev, with 17 days still unbooked and only 63 working days left in the year, is the example: unless his carry-over rules allow more than five days into next year, some of that leave will be lost.

Step 4 — Write the report

Write a summary sheet, one row per person with the flags highlighted, and the bookings behind every figure:

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

path = "leave_balances_2026-10-02.xlsx"
out = report.reset_index()[["Employee", "Name", "Team", "Entitlement", "Carry-over used", "Carry-over lost", "taken", "booked", "Remaining", "Flag"]]
with pd.ExcelWriter(path, engine="openpyxl", date_format="dd mmm yyyy", datetime_format="dd mmm yyyy") as xw:
    out.rename(columns={"taken": "Taken", "booked": "Booked"}).to_excel(xw, sheet_name="Balances", index=False)
    book.drop(columns="Work days").to_excel(xw, sheet_name="Bookings", index=False)
    pd.DataFrame({"Public holiday": BANK_HOLIDAYS[(BANK_HOLIDAYS >= YEAR_START) & (BANK_HOLIDAYS < YEAR_END)]}).to_excel(xw, sheet_name="Holidays", index=False)

wb = load_workbook(path)
ws = wb["Balances"]
n = ws.max_row
ws.conditional_formatting.add(f"A2:J{n}", FormulaRule(formula=["$I2<0"], fill=PatternFill("solid", fgColor="FFD7D5")))
ws.conditional_formatting.add(f"A2:J{n}", FormulaRule(formula=['AND($I2>=0,$J2<>"")'], fill=PatternFill("solid", fgColor="FFF4CE")))
for col, width in zip("ABCDEFGHIJ", (10, 16, 12, 12, 15, 15, 8, 8, 11, 44)):
    ws.column_dimensions[col].width = width
wb.save(path)
print(wb.sheetnames, "| flagged:", int((report["Flag"] != "").sum()))

Negative balances are shaded red and other flags amber, using formula rules so the shading follows the numbers if someone corrects a cell. The Bookings sheet lists every booking with the days it cost, so a disputed balance can be explained line by line, and the Holidays sheet shows which public holidays were skipped. Sending each manager only their team's rows is the pattern in Generate One Excel Report per Region in a Loop.

Common pitfalls

• Counting calendar days. Count working days with the person's own work pattern.
• Ignoring public holidays. Skip them, or a bank-holiday Monday costs a day of leave.
• Full-year entitlement for starters and leavers. Pro-rate by the days employed.
• Year-end bookings in one year. Clip each booking to the leave year.
• Carry-over that never expires. Apply the expiry rule your policy sets.

Conclusion

To build a leave balance report, count every booking in working days with numpy.busday_count, using each employee's work pattern as the weekmask and the public holiday calendar, clipped to the leave year, with half days as 0.5. Pro-rate entitlement by employment dates and working days, add carry-over only if used before it expires, subtract leave taken and booked, and flag negative and large balances in a workbook that shows the bookings behind every figure.

Frequently asked questions

How do I count working days between two dates in Python? Use numpy.busday_count with the start date and the day after the end date, a weekmask for the working days and a list of public holidays to skip.

How do I count leave for part-time employees? Pass their work pattern as the weekmask, for example "Mon Tue Wed", so only their working days are counted, and pro-rate their entitlement by days worked per week.

How do I handle leave bookings that span two leave years? Clip each booking's start and end to the leave year before counting, so each year counts only its own days.

How is pro-rata annual leave calculated? Multiply full-time entitlement by the share of the leave year employed and by days worked per week divided by five, then round according to policy.

Related

• Up one level: Building Finance and Operations Reports — finance reports from Python.
• Build a Headcount and Attrition Report — another HR report.
• Flag Public Holidays in Excel Data with Python — the holiday calendar.
• Generate a Staff Rota in Excel with Python — planning around leave.