Timesheet consolidation template
By the Analistable team · Updated · 2 min read
The workbook has a Timesheets sheet for every employee's daily entries, an Employees sheet with contracted weekly hours and a Projects sheet. Paste the week's entries, and formulas total hours and days logged per person, show overtime and shortfall against contract, and total hours by project with a check that nothing was missed.
Part of our guide: How to consolidate data in Excel
What the template does
Weekly timesheets usually come in as one sheet per person. Payroll wants hours per employee against their contract; project leads want hours per project. Both views come from the same stacked table, so the template keeps one row per person per day per project and summarises it two ways. It is the ready-to-use version of combining weekly timesheets.
Sheet-by-sheet guide
| Sheet | You fill in | Formulas add |
|---|---|---|
| Instructions | Nothing | — |
| Timesheets | Employee, Week ending, Date, Project, Hours | — |
| Employees | Employee, Contracted hours | Hours logged, Rows logged, Overtime, Shortfall |
| Projects | Project codes | Hours per project, total and check |
How to fill it in
- Copy each person's entries into Timesheets, one row per day and project, with their name on every row.
- List every employee once on Employees with their contracted weekly hours. Names must match the Timesheets sheet exactly.
- List project codes on Projects. The last row compares the project total with all hours logged.
- Use one week per copy, or add a Week ending condition to the formulas for several weeks.
How the formulas work
Hours and entry counts per employee use SUMIFS and COUNTIFS on the name:
=SUMIFS(Timesheets!$E$2:$E$100,Timesheets!$A$2:$A$100,A2)
=COUNTIFS(Timesheets!$A$2:$A$100,A2)Overtime and shortfall use MAX so neither goes negative:
=MAX(0,C2-B2)
=MAX(0,B2-C2)Worked example
Week ending 2026-09-19, three people, twelve entries. Mia worked a long Wednesday, Leo missed Friday and Raj is part-time on 20 hours:
| Employee | Contracted | Logged | Rows | Overtime | Shortfall |
|---|---|---|---|---|---|
| Mia | 37.5 | 40 | 5 | 2.5 | 0 |
| Leo | 37.5 | 35 | 4 | 0 | 2.5 |
| Raj | 20 | 18 | 3 | 0 | 2 |
On Projects, P-100 has 30 hours, P-200 35 and P-300 28. The 93-hour total equals all hours logged, so no entry uses a code missing from the list.
Limits and when to move on
- Rows logged counts entries, not distinct days. If someone splits a day across two projects it counts twice; treat it as a prompt to check, not a day count.
- Overtime is weekly against contract only, and it treats every logged hour the same, whether billable, training or leave. Daily overtime rules or rate multipliers need extra columns and a separate rate table.
- For payroll checks, see reconciling payroll with timesheets. If timesheets arrive as many files, stack them with Power Query from a folder or load them into Analistable and ask who is under contract hours.
Frequently asked questions
- Why does an employee show 0 hours?
- Their name on Employees does not match the Timesheets sheet exactly. Check spelling and trailing spaces.
- Can I consolidate a whole month?
- Yes. Paste every week into Timesheets and add a Week ending condition to the SUMIFS, or multiply contracted hours by the number of weeks.
- Does it work in Google Sheets?
- Yes. SUMIFS, COUNTIFS, MAX, IF and SUM behave the same in Google Sheets.