Calculate Working Hours Between Two Timestamps

"Resolved within 8 working hours" is how most service levels are written, and it is not the same as "resolved within 8 hours". A ticket opened at 16:45 on a Friday and resolved at 10:15 on Monday took 65 and a half hours on the clock, but only 2 hours of business time if the office is open from 09:00 to 17:30. Excel has NETWORKDAYS for whole days and nothing built in for hours, which is why SLA spreadsheets are full of long nested formulas that nobody wants to check. In Python the rule can be written once, readably, and then applied to every ticket. This page is part of Working with Dates and Times in Excel Data.

Prerequisites

pip install pandas numpy openpyxl xlsxwriter

tickets.xlsx holds eight support tickets with the time each was opened and resolved and a priority. They are chosen to hit the awkward cases: a ticket raised late on a Friday, one raised in the middle of the night, one raised and closed on a Saturday, one resolved before the office opened, one that runs across a bank holiday, and one still open. The office works 09:00–17:30, Monday to Friday, and Monday 31 August 2026 is a holiday.

Step 1 — A clear reference version

Write the rule in the most obvious way first: walk day by day from the opening date to the closing date, and on each working day add the part of the interval that falls inside office hours:

import numpy as np
import pandas as pd

OPEN, CLOSE = 9.0, 17.5                                   # 09:00 to 17:30
HOLIDAYS = np.array(["2026-08-31"], dtype="datetime64[D]")

def business_hours_loop(start, end):
    if pd.isna(start) or pd.isna(end) or end <= start:
        return np.nan if pd.isna(end) else 0.0
    total, day = 0.0, start.normalize()
    while day <= end.normalize():
        if np.is_busday(np.datetime64(day.date()), holidays=HOLIDAYS):
            a = max(start, day + pd.Timedelta(hours=OPEN))
            b = min(end, day + pd.Timedelta(hours=CLOSE))
            if b > a:
                total += (b - a).total_seconds() / 3600
        day += pd.Timedelta(days=1)
    return total

t = pd.read_excel("tickets.xlsx", parse_dates=["Opened", "Resolved"])
t["Clock hours"] = ((t["Resolved"] - t["Opened"]).dt.total_seconds() / 3600).round(2)
t["Business hours"] = [round(business_hours_loop(s, e), 2) for s, e in zip(t["Opened"], t["Resolved"])]
print(t[["Ticket", "Opened", "Resolved", "Clock hours", "Business hours"]].to_string(index=False))

np.is_busday decides whether a date is a working day, using the weekend mask — Monday to Friday by default — and the holiday list. On each such day, the interval is clipped to the office hours: the later of the ticket's start and 09:00, the earlier of its end and 17:30. Anything outside that window adds nothing. The results show the difference: T-5002 is 89.5 clock hours — it spans a weekend and the holiday — but 2 business hours; T-5003, raised on Friday night, starts counting only on Tuesday morning; T-5004, raised and closed on a Saturday, has zero business hours; T-5006, open for most of a week, is 32 business hours. The open ticket gets NaN rather than a number — it has not finished.

Step 2 — A vectorised version for large sheets

The loop is easy to check and slow for hundreds of thousands of tickets. The vectorised version counts full working days between the first and last day with np.busday_count, and adds the partial first and last days:

def business_hours(start, end):
    s, e_raw = pd.to_datetime(start), pd.to_datetime(end)
    e = e_raw.fillna(s)                                   # open items: measure nothing, mask later
    sd, ed = s.dt.normalize(), e.dt.normalize()
    hour_of = lambda ts, d: ((ts - d).dt.total_seconds() / 3600).clip(OPEN, CLOSE)
    sh, eh = hour_of(s, sd), hour_of(e, ed)
    as_days = lambda x: x.values.astype("datetime64[D]")
    s_bus = np.is_busday(as_days(sd), holidays=HOLIDAYS)
    e_bus = np.is_busday(as_days(ed), holidays=HOLIDAYS)
    full_days = np.busday_count(as_days(sd + pd.Timedelta(days=1)), as_days(ed), holidays=HOLIDAYS)
    same_day = np.where(s_bus, (eh - sh).clip(lower=0), 0.0)
    multi_day = (np.where(s_bus, CLOSE - sh, 0.0) + np.where(e_bus, eh - OPEN, 0.0)
                 + np.clip(full_days, 0, None) * (CLOSE - OPEN))
    hours = np.where(sd == ed, same_day, multi_day)
    return pd.Series(np.where(e > s, hours, 0.0), index=s.index).where(e_raw.notna())

t["Business hours (fast)"] = business_hours(t["Opened"], t["Resolved"]).round(2)
print((t["Business hours"].fillna(-1) == t["Business hours (fast)"].fillna(-1)).all())

Clipping each timestamp's hour of day to the 09:00–17:30 window handles tickets raised overnight: a ticket opened at 21:10 is treated as opened at 17:30 on that day, which contributes nothing, and one resolved at 08:45 as resolved at 09:00. busday_count(a, b) counts working days from a up to but not including b — exactly the full days strictly between the first and last day. The last line checks the fast version against the loop on the sample; before relying on it, run the same comparison on a few thousand random intervals, which is how this version was tested.

Step 3 — Flag SLA breaches

Service levels usually depend on priority. Map each priority to its target and compare; tickets still open are measured up to now, so a ticket can breach before it is closed:

SLA = {"High": 4, "Normal": 8, "Low": 24}
now = pd.Timestamp("2026-09-01 16:00")
t["Measured to"] = t["Resolved"].fillna(now)
t["Hours used"] = business_hours(t["Opened"], t["Measured to"]).round(2)
t["Target"] = t["Priority"].map(SLA)
t["Breach"] = t["Hours used"] > t["Target"]
t["Status"] = np.where(t["Resolved"].isna(), "open", "closed")
print(t[["Ticket", "Priority", "Status", "Hours used", "Target", "Breach"]].to_string(index=False))
print(f"breach rate: {t['Breach'].mean():.0%}")

Two tickets breach. T-5006 is plain: 32 business hours against a 24-hour target. T-5005 is the instructive one: raised at 08:15 on Wednesday, before the office opened, and resolved at 08:45 on Thursday, again before opening — every minute of Wednesday's office day counts, so it used 8.5 hours against a target of 8, although it was fixed before anyone arrived on Thursday. Whether that is a fair breach is a policy question the numbers make visible. Using the report's run time for open tickets makes the flag answer "is this ticket already out of SLA?", which is what a team lead wants from a morning report. Freeze now to the report's cut-off time rather than calling pd.Timestamp.now() in several places, so every row is measured against the same moment — and so the report can be rerun for yesterday with the same result, as in Make Excel Output Reproducible.

Step 4 — Write the SLA report

cols = ["Ticket", "Priority", "Opened", "Resolved", "Status", "Clock hours", "Hours used", "Target", "Breach"]
with pd.ExcelWriter("sla_report.xlsx", engine="xlsxwriter", datetime_format="ddd dd mmm hh:mm") as xw:
    t[cols].to_excel(xw, sheet_name="Tickets", index=False)
    wb, ws = xw.book, xw.sheets["Tickets"]
    red = wb.add_format({"bg_color": "#FCE9E9", "font_color": "#9C0006"})
    ws.conditional_format(1, 0, len(t), len(cols) - 1, {"type": "formula", "criteria": "=$I2=TRUE", "format": red})
    ws.set_column("A:B", 10)
    ws.set_column("C:D", 18)
    summary = t.groupby("Priority").agg(Tickets=("Ticket", "count"), Breaches=("Breach", "sum"),
                                        Median_hours=("Hours used", "median"))
    summary.to_excel(xw, sheet_name="Summary")
print(summary)

Common pitfalls

• Clock time instead of business time. Subtracting timestamps counts nights and weekends; clip to office hours on working days.
• Forgetting holidays. A bank holiday inside an interval adds a full day to the result unless it is in the holiday list.
• Timezones. Tickets logged in UTC and an office working in local time give answers off by hours. Convert to the office's timezone and drop the timezone before calculating — see Handle Timezones in Excel Timestamps with Python.
• Lunch breaks and shifts. Split days are several windows per day; loop over the windows in the reference version and sum.
• Different calendars per team. A team working Sunday to Thursday needs weekmask="Sun Mon Tue Wed Thu" in is_busday and busday_count.

Performance notes

The loop version handles a few thousand tickets in a fraction of a second, which is enough for most weekly reports. The vectorised version does 100,000 tickets in well under a second, because everything is a whole-column operation. Keep both: the loop is the specification that people can read and check against a handful of real tickets, and the vectorised function is tested against it.

Conclusion

Define office hours, working days and holidays once. Count business time by clipping each interval to the office window on each working day — as a loop for clarity, and with np.busday_count plus partial first and last days for speed — and check the two against each other. Map priorities to targets, measure open tickets up to a fixed report time, flag breaches and write the tickets and a per-priority summary to Excel.

Frequently asked questions

How do I calculate business hours between two datetimes in Python? For each working day between the two dates, add the part of the interval that falls inside office hours, skipping weekends and holidays with numpy.is_busday. For many rows, count the full working days with numpy.busday_count and add the partial first and last days.

Does Excel have a function for working hours? Not directly. NETWORKDAYS and NETWORKDAYS.INTL count whole working days; working hours need a longer formula that combines them with the start and end times, which is why many teams calculate it in Python instead.

How do I exclude public holidays? Pass an array of holiday dates to numpy.is_busday and numpy.busday_count through the holidays argument. Keep the list in a file or a sheet that someone maintains for each year.

What if the office works different days? Use the weekmask argument, for example weekmask="Sun Mon Tue Wed Thu" for a Sunday-to-Thursday week. Different hours per day, or split shifts, are easiest in the loop version with a list of windows per weekday.

Related

• Up one level: Working with Dates and Times in Excel Data — parsing, grouping and date arithmetic.
• Calculate Business Days Between Excel Dates with pandas — the whole-day version of this problem.
• Convert Excel Time Values and Durations — writing hours back as Excel durations.
• Build an On-Time Delivery Report — another report measured against promised times.