Build a Staff Utilisation Report from Timesheets

Professional services firms live on one number: utilisation, the share of people's available time spent on billable client work. Too low and the firm loses money; too high for too long and people burn out or quality drops. The calculation looks simple — billable hours divided by available hours — but both halves hide traps. Available hours depend on each person's contract, bank holidays and leave, not on a flat 40 hours a week; billable depends on which timesheet codes count; and a person who did not submit a timesheet shows as 0% utilised rather than as missing. This page builds a utilisation report from a timesheet export with pandas, handling capacity properly, comparing with targets per role, flagging missing timesheets and overload, and writing a heatmap to Excel. It belongs to Building Finance and Operations Reports.

Prerequisites

pip install pandas numpy openpyxl

Three inputs: timesheets.xlsx with one row per person, day and time code; people.xlsx with each person's team, role and contracted weekly hours (one person works 30 hours); and leave.xlsx with approved leave days. The period is the four weeks from 31 August to 25 September 2026; 31 August was a UK bank holiday. Codes starting PRJ- are billable client projects; INT- codes are internal.

Step 1 — Available hours: contracts, holidays and leave

Available hours per person per week are their contracted daily hours on each working day, minus bank holidays and approved leave:

import pandas as pd
import numpy as np

people = pd.read_excel("people.xlsx").set_index("Person")
leave = pd.read_excel("leave.xlsx")
ts = pd.read_excel("timesheets.xlsx")
HOLIDAYS = pd.to_datetime(["2026-08-31"])

days = pd.bdate_range("2026-08-31", "2026-09-25")
cal = pd.MultiIndex.from_product([people.index, days], names=["Person", "Date"]).to_frame(index=False)
cal["Contract h"] = cal["Person"].map(people["Weekly hours"] / 5)
cal.loc[cal["Date"].isin(HOLIDAYS), "Contract h"] = 0.0
cal = cal.merge(leave.rename(columns={"Hours": "Leave h"}), on=["Person", "Date"], how="left").fillna({"Leave h": 0})
cal["Available h"] = (cal["Contract h"] - cal["Leave h"]).clip(lower=0)
cal["Week"] = cal["Date"].dt.to_period("W-SUN").dt.start_time

available = cal.pivot_table(index="Person", columns="Week", values="Available h", aggfunc="sum")
print(available.round(1).rename(columns=lambda w: f"w/c {w:%d %b}").to_string())

Building a calendar of every person and working day first — MultiIndex.from_product — gives a complete grid to subtract holidays and leave from, so nobody's capacity depends on whether they happened to log time. The bank holiday removes a day from everyone's first week; B. Lee's three days of leave and D. Ortiz's day off reduce their weeks; C. Novak's 30-hour contract gives 6 hours a day rather than 7.5. Getting capacity right matters more than it seems: dividing by a flat 37.5 would make a part-timer look under-utilised every week.

Step 2 — Billable and non-billable hours

Classify each timesheet line by its code and total per person and week:

ts["Week"] = ts["Date"].dt.to_period("W-SUN").dt.start_time
ts["Kind"] = np.where(ts["Code"].str.startswith("PRJ-"), "Billable", "Non-billable")
ts["Category"] = ts["Code"].str.replace(r"^(PRJ|INT)-", "", regex=True)

logged = ts.pivot_table(index=["Person", "Week"], columns="Kind", values="Hours", aggfunc="sum", fill_value=0)
nonbill = ts[ts["Kind"] == "Non-billable"].pivot_table(index="Person", columns="Category", values="Hours", aggfunc="sum", fill_value=0)
print(nonbill.round(1).to_string())

Keeping the classification rule in one place — here, the PRJ- prefix — means the definition of "billable" is visible and consistent; if some project codes are non-billable (pre-sales work, fixed-fee overruns written off), add them to an explicit list instead of relying on the prefix. The non-billable breakdown into admin, training and sales answers the follow-up question every manager asks about a low utilisation figure: what was the rest of the time spent on?

Step 3 — Utilisation, targets and flags

Join logged hours onto the capacity grid, so every person and week appears even with no timesheet, and compute utilisation against a target that depends on the role:

TARGET = {"Senior": 0.65, "Consultant": 0.75, "Analyst": 0.70}

grid = (cal.groupby(["Person", "Week"])["Available h"].sum().to_frame()
           .join(logged, how="left").fillna(0))
grid["Logged h"] = grid["Billable"] + grid["Non-billable"]
grid["Utilisation"] = (grid["Billable"] / grid["Available h"]).where(grid["Available h"] > 0)
grid = grid.join(people[["Team", "Role"]], on="Person")
grid["Target"] = grid["Role"].map(TARGET)

def flag(r):
    if r["Available h"] > 0 and r["Logged h"] < 0.5 * r["Available h"]:
        return "timesheet missing or incomplete"
    if r["Logged h"] > 1.1 * r["Available h"]:
        return "logged > 110% of available"
    if r["Utilisation"] < r["Target"] - 0.10:
        return "well below target"
    return ""

grid["Flag"] = grid.apply(flag, axis=1)
print(grid.loc[grid["Flag"] != "", ["Available h", "Logged h", "Utilisation", "Target", "Flag"]].round(2).to_string())

Three flags cover the cases a manager should look at. A week where logged hours are under half of available is almost always a missing timesheet, not a quiet week — E. Kim logged nothing in the last week — and must be chased before the figures mean anything. Logged time well over available hours means overtime: C. Novak logged 33.3 hours against 30 available in the week of 14 September and stayed above 100% billable the week after. And utilisation more than ten points below the role's target is a resourcing conversation. Targets per role reflect that senior staff spend more time on management and sales than analysts.

Step 4 — Summaries by person and team

Utilisation for a period is total billable hours over total available hours — never the average of weekly percentages, which gives short weeks the same weight as full ones:

complete = grid[grid["Flag"] != "timesheet missing or incomplete"]
by_person = complete.groupby(["Person", "Team", "Role"])[["Available h", "Billable", "Non-billable"]].sum()
by_person["Utilisation"] = (by_person["Billable"] / by_person["Available h"]).round(3)
by_person["Target"] = by_person.index.get_level_values("Role").map(TARGET)
by_person["vs target"] = (by_person["Utilisation"] - by_person["Target"]).round(3)
by_team = complete.groupby("Team")[["Available h", "Billable"]].sum()
by_team["Utilisation"] = (by_team["Billable"] / by_team["Available h"]).round(3)
print(by_person[["Available h", "Billable", "Utilisation", "Target", "vs target"]].round(3).to_string())
print(by_team.round(3).to_string())

Weeks flagged as missing timesheets are excluded from the summary rather than counted as zero billable hours — including them would drag E. Kim's figure down for a reason that has nothing to do with how busy they were, and the report says plainly that one week is missing. Summing hours first and dividing once gives the correct weighted figure for each person and team. Over the four weeks, A. Shah sits right on the 65% senior target, B. Lee and D. Ortiz are about five points above theirs, and C. Novak is 16 points over — the overtime from the last fortnight showing through. E. Kim, at 53% over three complete weeks, is 17 points under the analyst target; with 24 hours of training logged, much of that gap is planned onboarding rather than idle time, which is exactly why the non-billable breakdown belongs next to the headline figure. At team level, Consulting runs at 78% and Data at 63%.

Step 5 — A heatmap in Excel

The person-by-week utilisation grid is easiest to read as a heatmap. Write it with a three-colour scale so low, on-target and overloaded weeks stand out:

from openpyxl.formatting.rule import ColorScaleRule
from openpyxl.styles import Font

heat = grid["Utilisation"].where(grid["Flag"] != "timesheet missing or incomplete").unstack("Week")
heat.columns = [f"w/c {w:%d %b}" for w in heat.columns]

with pd.ExcelWriter("utilisation_2026-09.xlsx", engine="openpyxl") as xw:
    heat.to_excel(xw, sheet_name="Heatmap")
    by_person.reset_index().to_excel(xw, sheet_name="By person", index=False)
    by_team.to_excel(xw, sheet_name="By team")
    grid.reset_index().to_excel(xw, sheet_name="Person-weeks", index=False)
    nonbill.to_excel(xw, sheet_name="Non-billable")
    ws = xw.sheets["Heatmap"]
    last_col = chr(ord("A") + heat.shape[1])
    rng_ = f"B2:{last_col}{heat.shape[0] + 1}"
    for row in ws[rng_]:
        for c in row:
            c.number_format = "0%"
    ws.conditional_formatting.add(rng_, ColorScaleRule(start_type="num", start_value=0.4, start_color="F8696B",
                                                       mid_type="num", mid_value=0.7, mid_color="FFEB84",
                                                       end_type="num", end_value=1.0, end_color="63BE7B"))
    for sheet in xw.book.worksheets:
        for c in sheet[1]:
            c.font = Font(bold=True)
        sheet.column_dimensions["A"].width = 14
print(heat.round(2).to_string())

The colour scale runs from red at 40% through yellow at 70% to green at 100%; the empty cell for E. Kim's missing week stays blank rather than red, which is the honest display. Percent formatting shows 78% rather than 0.78. Readers can scan down a column for a team's week or across a row for one person's month. More on colour scales in Add Data Bars and Colour Scales with openpyxl.

Common pitfalls

• A flat 37.5 or 40 hours as capacity. Use each contract, minus holidays and leave.
• Missing timesheets counted as zero. Flag and exclude them, or chase them first.
• Averaging weekly percentages. Sum hours, then divide.
• A fuzzy definition of billable. Classify codes in one explicit rule.
• One target for everyone. Set targets by role.

Conclusion

A utilisation report needs a proper denominator and an honest treatment of gaps. Build a calendar of available hours from contracts, bank holidays and leave; classify timesheet codes into billable and non-billable; join logged hours onto the calendar so every person-week appears; and compute utilisation against role targets, flagging missing timesheets, overload and large shortfalls. Summarise by summing hours before dividing, and present the person-by-week grid as a colour-scaled heatmap.

Frequently asked questions

How do I calculate utilisation from timesheets in Python? Divide billable hours by available hours, where available hours come from each person's contract minus bank holidays and leave. Compute both per person and week with pandas pivot tables.

How should missing timesheets affect utilisation? They should not count as zero billable time. Flag weeks where logged hours are far below available hours as missing, chase them, and exclude them from summaries until they arrive.

Should I average weekly utilisation percentages? No. Sum billable and available hours over the period and divide once, so weeks with less capacity do not count as much as full weeks.

How do I show utilisation as a heatmap in Excel? Write the person-by-week utilisation table and add an openpyxl ColorScaleRule over it, for example red at 40 percent, yellow at 70 percent and green at 100 percent, with a percent number format.

Related

• Up one level: Building Finance and Operations Reports — finance and operations reports in Excel.
• Consolidate Timesheets from Many Workbooks — getting the timesheet lines in the first place.
• Build a Project Budget Burn Report — the same hours seen from the project side.
• Generate a Staff Rota in Excel with Python — planning the hours rather than measuring them.