Calculate billable utilisation from an IT services timesheet

Settle the definition before the spreadsheet

Delivery heads, finance and HR often mean different things by "utilisation", and the argument that follows a monthly review is usually about the denominator, not the data. Write the definition at the top of the report:

Some firms use 9 hours, some exclude training from the denominator, some count only client-approved hours as billable. Any of these is fine if it is stated and applied consistently. What is not fine is a dashboard that silently changes definition between months.

Keep the billable flag in a project master

Do not hard-code "if the project name contains ACME it is billable". Keep a small table with one row per project code and a billable yes/no column, owned by whoever opens projects. The join to it must be many-to-one, and every timesheet line must find a match; a new project code that nobody added to the master should stop the report, not be treated as non-billable by default.

Computing utilisation per person and per team

The employees, projects and hours below are synthetic, covering one week.

import pandas as pd

# Synthetic timesheet for one fortnight at a small IT services firm.
ts = pd.DataFrame({
    "emp": ["E01"] * 5 + ["E02"] * 4 + ["E03"] * 4,
    "date": pd.to_datetime(["2026-10-05", "2026-10-06", "2026-10-07", "2026-10-08", "2026-10-09",
                            "2026-10-05", "2026-10-06", "2026-10-07", "2026-10-08",
                            "2026-10-05", "2026-10-06", "2026-10-07", "2026-10-09"]),
    "project": ["ACME-DEV", "ACME-DEV", "INTERNAL-TRAINING", "ACME-DEV", "BENCH",
                "GLOBEX-SUPPORT", "GLOBEX-SUPPORT", "GLOBEX-SUPPORT", "PRESALES",
                "ACME-DEV", "ACME-DEV", "ACME-DEV", "ACME-DEV"],
    "hours": [8, 9, 8, 7.5, 8, 8, 8, 10, 6, 8, 8, 8, 12],
})
projects = pd.DataFrame({
    "project": ["ACME-DEV", "GLOBEX-SUPPORT", "INTERNAL-TRAINING", "PRESALES", "BENCH"],
    "billable": [True, True, False, False, False],
})
# Capacity: working days in the period minus approved leave, 8 hours a day.
calendar_days = pd.bdate_range("2026-10-05", "2026-10-09")   # Mon-Fri, no holiday this week
leave = {"E02": ["2026-10-09"]}                              # approved leave dates
STD_HOURS = 8

ts = ts.merge(projects, on="project", how="left", validate="many_to_one")
assert ts["billable"].notna().all(), "unmapped project code"

# Flag days booked beyond a sensible cap before they inflate utilisation.
daily = ts.groupby(["emp", "date"])["hours"].sum()
print("days over 10 h:", {f"{e} {d.date()}": h for (e, d), h in daily[daily > 10].items()})

s = ts.groupby("emp").agg(
    booked=("hours", "sum"),
    billable=("hours", lambda h: h[ts.loc[h.index, "billable"]].sum()))
expected = {e: calendar_days.difference(pd.to_datetime(leave.get(e, []))) for e in s.index}
s["available"] = [len(expected[e]) * STD_HOURS for e in s.index]
s["billable_util_%"] = (100 * s["billable"] / s["available"]).round(1)
s["booked_vs_available_%"] = (100 * s["booked"] / s["available"]).round(1)
print(s.to_string())

# Working days with no entry at all: chase these before publishing the numbers.
for e, days in expected.items():
    booked_days = set(ts.loc[ts["emp"].eq(e), "date"])
    missing = [d.date().isoformat() for d in days if d not in booked_days]
    if missing:
        print("missing timesheet:", e, missing)

team = s[["billable", "available"]].sum()
print("\nteam billable utilisation:", round(100 * team["billable"] / team["available"], 1), "%")
print("average of individual %:", round(s["billable_util_%"].mean(), 1), "%  <- do not report this")

Three things come out before the utilisation table. E03 booked 12 hours on Friday 9 October and nothing on Thursday 8 October: most likely Thursday's work was entered on Friday, which is harmless for the weekly total and wrong for any day-level view. E02's Friday leave reduces their available hours to 32, so their 26 billable hours give 81.2%, rather than the 65% you would get by dividing by a full week. E01 booked 40.5 hours but only 24.5 were billable, with training and bench time making up the rest.

Why the team figure is computed separately

The last two lines print 77.2% and 77.5%. The first adds billable and available hours across the team and divides once. The second averages the three individual percentages, which gives E02's 32-hour week the same weight as the others' 40-hour weeks. The gap is small here and can be large in a month with many leave days or new joiners. Report the pooled figure, and apply the same rule when rolling people up to a project, an account or a practice.

Excel version

With the timesheet as an Excel table named TS (columns Emp, Date, Project, Hours) and the project master as PM (Project, Billable), add a Billable column to TS:

=XLOOKUP([@Project], PM[Project], PM[Billable], "UNMAPPED")

Then on a summary sheet with one row per employee:

Billable hours    =SUMIFS(TS[Hours], TS[Emp], A2, TS[Billable], TRUE)
Available hours   =NETWORKDAYS(Start, End, Holidays[Date]) * 8 - LeaveDays * 8
Utilisation       =IFERROR(B2 / C2, "")

Holidays is a one-column table of company holidays for the year, maintained by HR. Put a count of "UNMAPPED" rows at the top of the sheet in red; it should be zero before anyone reads the percentages. If your office works alternate Saturdays, NETWORKDAYS will not model it; see count working days when the 2nd and 4th Saturdays are off.

Checks that belong in the monthly routine

• Missing days. Every expected working day per person should have at least one entry. Send the list to managers before closing the month.
• Long days. Flag any day above a threshold agreed with delivery, such as 10 hours. It might be real overtime or it might be catch-up entries; either way someone should confirm.
• Unmapped codes. New project codes that are not in the master.
• Leave agreement. Compare leave booked in the timesheet with leave approved in the HR system. Where they differ, the HR record should drive available hours.

What the number is for

Utilisation tells you whether capacity is being used on paid work. It does not tell you whether the work was profitable, whether the client accepted the hours, or whether a person is performing well; a high figure can mean an understaffed project and a low one can mean a sensible training month. Keep the booked-vs-available column beside it so readers can see overtime, and publish the list of missing days alongside the result rather than hiding it.

Timesheet and capacity reporting like this is typical early work for analysts in IT services and consulting firms. Our Data Analyst programme covers the Excel, SQL and Power BI side of it with practice datasets, and the free skill check is a quick way to see which of those areas you already know.