Project hours vs budget template
By the Analistable team · Updated · 2 min read
The workbook has a Timesheet sheet and a Budget sheet. Paste time entries with a project code, hours and rate, list each project's budgeted hours and cost, and formulas total hours and cost used, show hours left and percent used, flag projects at risk or over, and highlight time logged to unknown codes.
Part of our guide: How to join spreadsheets on a common column
What the template does
Time sits in a timesheet export; budgets sit in a project plan. Comparing them by hand means filtering the timesheet for each project and adding up. This template joins the two on the project code so every project shows budget against actual in one row. It is the workbook version of matching timesheet hours to projects and uses the same variance logic as budget vs actuals.
Sheet-by-sheet guide
| Sheet | You fill in | Formulas add |
|---|---|---|
| Instructions | Nothing | — |
| Timesheet | Date, Person, Project code, Hours, Hourly rate | Cost, Code in budget? |
| Budget | Project code, Project, Budget hours, Budget cost | Hours used, Cost used, Hours left, % used, Status |
How to fill it in
- Export time entries from your time-tracking tool for the period and paste them into Timesheet from A2. Add the person's hourly cost rate in column E if the export lacks it.
- List each project once in Budget with its code, name, budgeted hours and budgeted cost.
- Fill formula columns down to your last row. Any timesheet line showing Check code has a project code missing from Budget.
- Sort Budget by % hours used to see which projects need attention first.
How the formulas work
Hours and cost used are SUMIFS on the project code:
=SUMIFS(Timesheet!$D$2:$D$100,Timesheet!$C$2:$C$100,A2)
=SUMIFS(Timesheet!$F$2:$F$100,Timesheet!$C$2:$C$100,A2)Status flags a project at 80% of budgeted hours and marks it Over once it passes 100%:
=IF(E2>C2,"Over",IF(E2/C2>=0.8,"At risk","On track"))Worked example
Ten sample time entries across three people. Two hours are logged to P-999, which is not in the budget, so they appear in no project total until the code is fixed.
| Code | Budget hrs | Hours used | Cost used | Hours left | % used | Status |
|---|---|---|---|---|---|---|
| P-100 | 40 | 20 | 1660 | 20 | 0.5 | On track |
| P-200 | 20 | 21 | 1470 | -1 | 1.05 | Over |
| P-300 | 18 | 16 | 1400 | 2 | 0.889 | At risk |
Limits and when to move on
- Codes must match exactly. A code typed as 'P100' in the time tool and 'P-100' in the plan will not join; fix the codes or add a mapping column.
- The template compares to a single total budget, not a phased monthly budget. Add a Month column to both sheets and a month condition to the SUMIFS if you need phasing.
- When the timesheet is several exports — one per team or per month — combine the weekly timesheets first, or load the files into Analistable and ask which projects are over budget by person.
Frequently asked questions
- Can I track cost instead of hours?
- Both are tracked. Cost used multiplies each entry's hours by its rate, so compare column F with Budget cost in column D for a money view.
- Why does a project show 0 hours used?
- No timesheet line has exactly that code. Check for extra spaces or a different format, and look for Check code lines on the Timesheet sheet.
- Does it work in Google Sheets?
- Yes. Upload the file to Google Drive and open it with Google Sheets; the formulas need no changes.