Build a Support Ticket SLA Report

A service desk promises its customers that an urgent ticket gets a first response within an hour and a fix within a working day, and a low-priority one within two days. Whether it keeps that promise is a question the helpdesk tool's built-in dashboards often answer badly: they count calendar hours, so a ticket logged at 16:55 on Friday "breaches" over the weekend; they cannot combine data from two tools; and they do not produce the monthly Excel pack the customer contract asks for. Calculating SLA attainment from the raw ticket export solves all three. The central piece is measuring elapsed time in business hours; the rest is grouping and presentation. This page builds the calculation, flags breaches and tickets about to breach, summarises attainment, and writes the report. It belongs to Building Finance and Operations Reports.

Prerequisites

pip install pandas numpy openpyxl

tickets.xlsx is a September 2026 export with one row per ticket: ID, created time, priority (P1 most urgent), the team that handled it, the time of the first response, and the resolved time (blank for tickets still open). Service hours are 09:00–17:00 Monday to Friday, UK bank holidays excluded, and the report is run at 17:00 on 30 September.

Step 1 — Define the targets and the service calendar

SLA targets are stated in business hours per priority. Keep them, the working day and the holidays as data:

import pandas as pd
import numpy as np

SLA = pd.DataFrame({"Priority": ["P1", "P2", "P3"],
                    "Response h": [1, 4, 8],
                    "Resolve h": [8, 24, 40]}).set_index("Priority")
DAY_START, DAY_END = 9, 17                         # service hours
HOLIDAYS = pd.to_datetime(["2026-08-31", "2026-12-25", "2026-12-28"])
AS_OF = pd.Timestamp("2026-09-30 17:00")

tickets = pd.read_excel("tickets.xlsx")
tickets = tickets.join(SLA, on="Priority", validate="many_to_one")
print(SLA.to_string())
print(len(tickets), "tickets |", tickets["Resolved"].isna().sum(), "still open")

A P2's resolution target of 24 business hours means three working days, not one calendar day. Joining the targets onto each ticket with validate="many_to_one" guards against a priority appearing twice in the targets table; a ticket with an unknown priority would get NaN targets, which is worth asserting against in a real job.

Step 2 — Elapsed time in business hours

The number of business hours between two timestamps counts only the parts of each working day inside service hours. Whole days in between are counted with NumPy's business-day functions, which also skip holidays; the first and last day are clipped to the service window:

HOURS_PER_DAY = DAY_END - DAY_START
BUSDAY = np.busdaycalendar(weekmask="1111100", holidays=HOLIDAYS.values.astype("datetime64[D]"))

def clip_to_day(ts):
    """Hours of ts's own day that fall inside service hours, up to ts."""
    if not np.is_busday(np.datetime64(ts.date()), busdaycal=BUSDAY):
        return 0.0
    t = ts.hour + ts.minute / 60 + ts.second / 3600
    return min(max(t, DAY_START), DAY_END) - DAY_START

def business_hours(start, end):
    if pd.isna(start) or pd.isna(end) or end <= start:
        return 0.0 if not (pd.isna(start) or pd.isna(end)) else np.nan
    d0, d1 = np.datetime64(start.date()), np.datetime64(end.date())
    full_days = np.busday_count(d0, d1, busdaycal=BUSDAY)      # business days from d0 up to (not incl.) d1
    hours = full_days * HOURS_PER_DAY - clip_to_day(start) + clip_to_day(end)
    return round(hours, 4)

checks = [("2026-09-25 16:50", "2026-09-28 09:20"),     # Friday afternoon to Monday morning
          ("2026-09-30 08:00", "2026-09-30 12:30"),     # starts before opening
          ("2026-08-28 16:00", "2026-09-01 10:00")]     # across a bank holiday weekend
for a, b in checks:
    print(f"{a} → {b}: {business_hours(pd.Timestamp(a), pd.Timestamp(b))} business hours")

The trick is in full_days * HOURS_PER_DAY - clip_to_day(start) + clip_to_day(end): count every business day from the start date up to the end date as a full day, take off the part of the first day before the ticket arrived, and add the part of the last day up to the end. Ticket INC-5181, logged at 16:50 on a Friday and answered at 09:20 on Monday, took 30 business minutes (0.5 hours) — not 64 calendar hours. A ticket logged at 08:00 before opening starts its clock at 09:00, and the August bank holiday Monday is skipped entirely. These three cases are the ones to keep as unit tests, because they are where hand-rolled business-time calculations usually go wrong.

Step 3 — Breaches and tickets at risk

Apply the calculation to every ticket. Resolved tickets are measured to their resolution; open tickets are measured to the report time, which shows how much of their allowance is used up:

tickets["Response bh"] = [business_hours(a, b) for a, b in zip(tickets["Created"], tickets["First response"])]
tickets["Elapsed bh"] = [business_hours(a, b if pd.notna(b) else AS_OF)
                         for a, b in zip(tickets["Created"], tickets["Resolved"])]
tickets["Response met"] = tickets["Response bh"] <= tickets["Response h"]
tickets["Resolve met"] = np.where(tickets["Resolved"].notna(), tickets["Elapsed bh"] <= tickets["Resolve h"], np.nan)
tickets["Used %"] = (tickets["Elapsed bh"] / tickets["Resolve h"] * 100).round(0)

open_ = tickets[tickets["Resolved"].isna()]
at_risk = open_[open_["Used %"] >= 75].sort_values("Used %", ascending=False)
print("open:", len(open_), "| at risk (≥75% of allowance used):", len(at_risk),
      "| already breached:", int((open_["Used %"] > 100).sum()))
print(at_risk[["Ticket", "Priority", "Team", "Elapsed bh", "Resolve h", "Used %"]].round(1).head(5).to_string(index=False))

Resolve met is left empty (NaN) for open tickets: they have neither met nor missed their target yet, and counting them as met would flatter the figures. Instead, open tickets get a "used" percentage, and those at 75% or more of their allowance go on an at-risk list — the most useful part of the report for the team lead, because those tickets can still be saved. Tickets already past 100% are breaches in waiting and are listed first. In this export, 16 tickets are open and four are at risk: INC-5186, a P2 open since 22 September, has used 208% of its 24-hour allowance and has already breached; INC-5184, a P3, has used 39 of its 40 hours; and two P2s are at 94–95%.

Step 4 — Attainment by priority, team and week

SLA attainment is the share of tickets that met their target. Compute it from resolved tickets only, for resolution, and from all responded tickets for first response:

resolved = tickets[tickets["Resolved"].notna()].copy()
resolved["Resolve met"] = resolved["Resolve met"].astype(bool)

by_priority = pd.DataFrame({
    "Tickets": tickets.groupby("Priority").size(),
    "Response met %": (tickets.groupby("Priority")["Response met"].mean() * 100).round(1),
    "Resolved": resolved.groupby("Priority").size(),
    "Resolve met %": (resolved.groupby("Priority")["Resolve met"].mean() * 100).round(1),
    "Median resolve bh": resolved.groupby("Priority")["Elapsed bh"].median().round(1),
})
by_team = (resolved.pivot_table(index="Team", columns="Priority", values="Resolve met", aggfunc="mean") * 100).round(1)
weekly = (resolved.set_index("Resolved").groupby(pd.Grouper(freq="W-SUN"))["Resolve met"].mean() * 100).round(1)
print(by_priority.to_string())
print(by_team.to_string())
print(weekly.rename(lambda d: f"w/e {d:%d %b}").to_string())

In September, 97–99% of resolved tickets met their resolution target in every priority, with median resolution times of 3.7, 9.9 and 17.0 business hours for P1, P2 and P3. The misses cluster in the Network team's P1 and P2 tickets. The last weekly bucket, ending 4 October, holds only the three days to the report date, so treat its lower figure with care. The table per priority is the headline: how many tickets, the share answered and resolved within target, and the median business hours to resolve. The team-by-priority pivot shows where misses concentrate, and the weekly series shows whether things are getting better. Medians are used instead of means because ticket durations are heavily skewed — one ticket open for a week would drag a mean far away from the typical experience.

Step 5 — Write the report

The workbook puts what needs action first — the at-risk list — then the attainment tables and the full ticket list:

from openpyxl.styles import Font, PatternFill

with pd.ExcelWriter("sla_report_2026-09.xlsx", engine="openpyxl") as xw:
    at_risk[["Ticket", "Priority", "Team", "Created", "Elapsed bh", "Resolve h", "Used %"]].to_excel(xw, sheet_name="At risk", index=False)
    by_priority.to_excel(xw, sheet_name="By priority")
    by_team.to_excel(xw, sheet_name="By team")
    weekly.rename("Resolve met %").to_frame().to_excel(xw, sheet_name="Weekly")
    tickets.to_excel(xw, sheet_name="Tickets", index=False)
    red, amber = PatternFill("solid", fgColor="FFC7CE"), PatternFill("solid", fgColor="FFEB9C")
    ws = xw.sheets["At risk"]
    for row in ws.iter_rows(min_row=2):
        row[-1].fill = red if (row[-1].value or 0) > 100 else amber
    for sheet in xw.book.worksheets:
        for c in sheet[1]:
            c.font = Font(bold=True)
        sheet.freeze_panes = "A2"
        for col in sheet.columns:
            sheet.column_dimensions[col[0].column_letter].width = 16
print("sheets:", ["At risk", "By priority", "By team", "Weekly", "Tickets"])
print("overall resolve met:", round(resolved["Resolve met"].mean() * 100, 1), "%")

Breached open tickets are shaded red and those approaching breach amber. Datetimes from pandas are naive here, so openpyxl writes them as ordinary Excel dates; if your export has time zones, convert to local time first. The full ticket list with every computed column — response and resolution business hours, met flags, percentage used — lets anyone audit a figure in the summary by filtering. For the monthly management pack, the headline attainment figures feed straight into Build a Monthly KPI Scorecard with Targets.

Common pitfalls

• Calendar hours. Weekends and nights make the desk look worse than it is; measure business hours.
• Counting open tickets as met. Leave them out of resolution attainment and show them as at risk.
• Forgetting holidays. Pass them to the business-day calendar.
• Means for durations. A few long tickets distort the mean; report medians.
• Unknown priorities. A ticket without a target silently drops out; assert every priority is known.

Conclusion

An SLA report from a ticket export rests on one well-tested function: business hours between two timestamps, counting whole business days with numpy.busday_count and clipping the first and last day to the service window. With it, compare each ticket's response and resolution times with the targets for its priority, measure open tickets to the report time to find those at risk, summarise attainment by priority, team and week from resolved tickets, and write a workbook that puts the at-risk list first.

Frequently asked questions

How do I calculate business hours between two dates in Python? Count the business days between the two dates with numpy.busday_count using a calendar that excludes weekends and holidays, multiply by the hours per day, subtract the hours of the first day before the start, and add the hours of the last day up to the end.

How do I calculate SLA attainment in pandas? Join the target hours per priority onto each ticket, compute elapsed business hours, mark each resolved ticket as met if the elapsed time is within target, and take the mean of that flag by priority, team or week.

How should open tickets be handled in an SLA report? Exclude them from resolution attainment, measure their elapsed time to the report time, and list those that have used most of their allowance as at risk.

Why use the median resolution time instead of the mean? Ticket durations are skewed by a few very long tickets, which pull the mean up. The median reflects the typical ticket.

Related

• Up one level: Building Finance and Operations Reports — finance and operations reports in Excel.
• Build an On-Time Delivery Report — another promised-versus-actual report.
• Calculate Working Hours Between Two Timestamps — the business-hours calculation in more depth.
• Build a Monthly KPI Scorecard with Targets — where attainment figures end up.