Consolidate Timesheets from Many Workbooks with Python

A timesheet template works well for the people filling it in: their name and the week at the top, projects down the side, days across, hours in the grid. It works badly for whoever has to add up thirty of them on Monday morning. The consolidation is mechanical — open each file, pick out the name, week and grid, stack the results — but the real work is in what goes wrong: someone has not submitted, someone sent a corrected version, a project code is mistyped, a day has fourteen hours. This page, part of Building Finance and Operations Reports, builds a consolidation that handles all of those and produces hours by project and person.

For combining files that are already simple tables, Combine Multiple Excel Files into One with Python is the simpler starting point; this page is about template-shaped files with header fields and a grid.

Prerequisites

pip install pandas openpyxl

The timesheets folder holds five files for the week ending 27 September 2026: Ana, Ben and Cai submitted once; Dee submitted twice, the second time correcting a 14-hour Tuesday. staff_and_projects.xlsx lists five staff with their contracted hours — Eli has not submitted — and the valid project codes with their client and whether they are billable.

Step 1 — Read one timesheet

Each file has the name in B2, the week ending in B3 and the grid starting at row 5 with a header of Project and the seven days, ending at a Total row. Read the header cells with openpyxl and the grid with pandas, stopping at the Total row:

from pathlib import Path
import pandas as pd
from openpyxl import load_workbook

DAYS = ["Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun"]

def read_timesheet(path):
    ws = load_workbook(path, read_only=True, data_only=True)["Timesheet"]
    name, week = ws["B2"].value, ws["B3"].value
    rows = list(ws.iter_rows(min_row=5, values_only=True))
    header, body = rows[0], []
    for row in rows[1:]:
        if row[0] in (None, "Total"):
            break
        body.append(row)
    grid = pd.DataFrame(body, columns=header)
    long = grid.melt(id_vars="Project", value_vars=DAYS, var_name="Day", value_name="Hours").dropna(subset=["Hours"])
    long["Hours"] = long["Hours"].astype(float)
    return long.assign(Name=name, Week=pd.Timestamp(week), File=Path(path).name)

print(read_timesheet("timesheets/ana_week39.xlsx").to_string(index=False))

melt turns the seven day columns into a Day column and an Hours column; dropping empty cells leaves only the hours actually entered, and converting to float gives one numeric type whether people typed 8 or 7.5. data_only=True matters if the template has formulas, such as a daily total: it reads their cached values rather than the formula text. Stopping at the Total row, instead of reading a fixed number of rows, copes with people adding a project row.

Step 2 — Read every file, keeping only the latest version per person

Read the whole folder. When someone has sent more than one file for the same week, keep the most recently modified and log the one it replaced:

files = sorted(Path("timesheets").glob("*.xlsx"))
frames = []
for f in files:
    ts = read_timesheet(f)
    frames.append(ts.assign(Modified=pd.Timestamp(f.stat().st_mtime, unit="s")))
hours = pd.concat(frames, ignore_index=True)

latest = hours.groupby(["Name", "Week"])["Modified"].transform("max")
replaced = hours.loc[hours["Modified"] < latest, ["Name", "File"]].drop_duplicates()
hours = hours[hours["Modified"] == latest].drop(columns="Modified")
print(len(files), "files;", "replaced:", replaced["File"].tolist())
print(hours.groupby("Name")["Hours"].sum().to_dict())

Dee's first file, with the 14-hour Tuesday, is superseded by the corrected one. Using the file's modification time works for files saved into a folder; if files arrive by email, use the received time or a version number in the file name instead, because copying a file can reset its modification time. Keep the list of replaced files — it goes on the exceptions sheet.

Step 3 — Check who is missing and what does not add up

Now compare the submissions with the reference data: staff with no timesheet, project codes that do not exist, days over a plausible maximum, and weeks short of contracted hours:

staff = pd.read_excel("staff_and_projects.xlsx", sheet_name="Staff")
projects = pd.read_excel("staff_and_projects.xlsx", sheet_name="Projects")

issues = []
for name in sorted(set(staff["Name"]) - set(hours["Name"])):
    issues.append({"Name": name, "Issue": "no timesheet submitted"})
for name in sorted(set(hours["Name"]) - set(staff["Name"])):
    issues.append({"Name": name, "Issue": "name not on the staff list"})
for r in hours[~hours["Project"].isin(projects["Code"])].itertuples():
    issues.append({"Name": r.Name, "Issue": f"unknown project {r.Project} ({r.Hours:g}h on {r.Day})"})
daily = hours.groupby(["Name", "Day"])["Hours"].sum()
for (name, day), h in daily[daily > 12].items():
    issues.append({"Name": name, "Issue": f"{h:g} hours on {day}"})
weekly = hours.groupby("Name", as_index=False)["Hours"].sum().merge(staff, on="Name")
for r in weekly[weekly["Hours"] < weekly["Contracted hours"]].itertuples(index=False, name=None):
    issues.append({"Name": r[0], "Issue": f"{r[1]:g} of {r[2]:g} contracted hours"})
for name, f in replaced.itertuples(index=False, name=None):
    issues.append({"Name": name, "Issue": f"superseded file {f} ignored"})
exceptions = pd.DataFrame(issues)
print(exceptions.to_string(index=False))

Eli has not submitted, Cai booked six hours to P-999, which is not a project, and Ben recorded 30 of 37.5 contracted hours — perhaps a day of leave that should be booked to a leave code. Dee's corrected Tuesday of 10 hours passes the 12-hour check; the original 14 would have failed it, and the superseded file is logged. Each issue names the person, so the list can be sent straight back to them.

Step 4 — Summarise hours by project and person

With the data in long format, every summary is a pivot. Join the project list to get the client and billable flag, then build the views managers ask for:

hours = hours.merge(projects.rename(columns={"Code": "Project"}), on="Project", how="left")
by_project = hours.pivot_table(index=["Client", "Project"], columns="Name", values="Hours", aggfunc="sum",
                               fill_value=0, margins=True, margins_name="Total")
print(by_project.to_string())

billable = hours.groupby("Name").apply(lambda g: g.loc[g["Billable"] == True, "Hours"].sum() / g["Hours"].sum(),
                                       include_groups=False)
print((billable * 100).round(1).to_dict())

The left merge keeps P-999's hours with an empty client rather than dropping them, and pivot_table then leaves them out of the project view because its client is missing — the exceptions sheet is where they appear until corrected. Billable utilisation — the share of each person's hours on billable projects — is the figure operations managers watch most; Ben's administrative day brings Ben's figure down to 75%. Cai's figure is lower than it will be once P-999 is corrected, because those six hours count towards Cai's total but not as billable.

Step 5 — Write the consolidated workbook

Write the project summary, the long-format detail for anyone who wants to pivot it differently, and the exceptions:

with pd.ExcelWriter("timesheets_week_2026-09-27.xlsx", engine="openpyxl") as writer:
    by_project.to_excel(writer, sheet_name="By project")
    hours[["Week", "Name", "Client", "Project", "Day", "Hours", "Billable", "File"]].to_excel(writer, sheet_name="Detail", index=False)
    exceptions.to_excel(writer, sheet_name="Exceptions", index=False)
print(pd.ExcelFile("timesheets_week_2026-09-27.xlsx").sheet_names)

The Detail sheet records the source file of every row, so a figure in the summary can be traced to the timesheet it came from. Running this on a schedule the morning after the deadline, and emailing the exceptions to each person named, turns chasing timesheets into a reply to an automatic message.

Common pitfalls

• Fixed grid sizes. Stop at the Total row rather than reading a set number of rows.
• Formulas in the template. Use data_only=True so totals are values, not formula text.
• Duplicate submissions. Keep the latest per person and week; log what was replaced.
• Silent unknown codes. Left-merge the project list and report unmatched codes.
• Name mismatches. "Ben Okafor" and "B. Okafor" do not match; take names from a dropdown in the template, or better, an employee ID.
• Missing people. Compare against the staff list; an absent file produces no error on its own.

Performance notes

Opening workbooks dominates the run time — roughly tens of milliseconds each in read-only mode — so a few hundred timesheets take seconds. If thousands arrive, read them in parallel.

Conclusion

Read each timesheet's header cells and grid, stopping at the Total row, and melt the grid into one row per person, project and day. Keep only the latest file per person and week, compare submitters with the staff list, and check project codes, daily maximums and contracted hours. Join the project list, pivot hours by project and person, and write the summary, the traceable detail and the exceptions to one workbook.

Frequently asked questions

How do I combine many timesheet workbooks into one table? Loop over the files, read the header cells (name, week) with openpyxl, read the day-by-project grid below them, and melt the grid so each row is one person, project and day. Concatenate those long tables and pivot as needed.

Why unpivot the timesheet grid? A grid with days as columns is easy to fill in but awkward to total. In long format — one row per person, project and day — hours by project, by person, by client or by day are all a single groupby or pivot_table.

How do I find out who has not submitted a timesheet? Compare the names read from the submitted files with the staff list. Anyone on the staff list with no file for the week is missing; anyone who submitted but is not on the list is a name mismatch to check.

What if someone sends a corrected timesheet? Keep only the latest file per person and week, using the file's modification time or a version in the file name, and log which file replaced which so the correction is traceable.

Related

• Up one level: Building Finance and Operations Reports — the pipeline these reports share.
• Combine Multiple Excel Files into One with Python — the simpler case of plain tables.
• Extract Report Header Fields from an Excel Sheet — reading fields above a table.
• Watch a Folder and Process New Excel Files — consolidating as files arrive.