The fastest way to track job hours in Excel is to use a simple table with columns for Date, Job, Task, Start Time, End Time, and Hours, then calculate daily totals with a SUM formula. For a ready-made solution, Microsoft offers free built-in timesheet templates under File > New (search “time sheet”), and you can customize them per job by adding a Job column and using a PivotTable to summarize hours by job.
Why Use Excel for Timesheet Tracking?
Excel remains a popular choice for small businesses and freelancers because it is flexible, offline, and requires no subscription beyond Office. Unlike dedicated time-tracking apps, Excel lets you shape the sheet to your exact workflow—add columns for client, project phase, billable status, or notes. You can also use formulas to compute overtime, calculate pay, or create invoices directly from logged hours. However, Excel lacks automatic timers and real-time collaboration (unless you use Excel for the web), so it suits people who log hours at the end of the day rather than in real time.
Core Components of a Job-Tracking Timesheet
A robust timesheet for job tracking should include these elements:
- Employee/User name (if multiple people use the same file)
- Date – use a real date format, not text
- Job or Project name – this is what you’ll filter or group by
- Task or Activity (optional but helpful for detailed billing)
- Start time and End time – use time format (e.g., 9:00 AM)
- Hours worked – calculated with a formula
- Billable flag (Yes/No) if you need to separate billable from non-billable
- Notes – for context like “client call” or “site visit”
For example, a simple layout: A1=Date, B1=Job, C1=Task, D1=Start, E1=End, F1=Hours. Enter times as actual time values (e.g., 9:00 AM) and in F2 use =IF(OR(D2="",E2=""),"",(E2-D2)*24) to get hours as a decimal number (e.g., 8.5 for 8 hours 30 minutes). The IF prevents errors when a row is incomplete.
How to Build a Basic Job-Tracking Timesheet from Scratch
Follow these steps to create a functional timesheet in about ten minutes:
- Open a new Excel workbook and rename the first sheet “Timesheet”.
- In row 1, enter headers: Date, Job, Task, Start, End, Hours, Billable, Notes.
- Format columns A as Date (e.g., mm/dd/yyyy), D and E as Time (e.g., 1:30 PM), and F as Number with 2 decimals.
- In F2, enter the formula
=IF(OR(D2="",E2=""),"",(E2-D2)*24)and drag down to fill for as many rows as you need (e.g., to F100). - Add a total row at the bottom: in F101, use
=SUM(F2:F100). - To summarize by job, create a PivotTable: select the data range (A1:H100), go to Insert > PivotTable, then drag “Job” to Rows and “Hours” to Values (set Value Field Settings to Sum).
- Protect the formula column by selecting F2:F100, then Home > Format > Lock Cell, and then Review > Protect Sheet (leave the “Select locked cells” unchecked if you want to prevent edits).
This gives you a daily log and an automatic per-job summary. For a weekly view, add a column for Week Number using =ISOWEEKNUM(A2) (Excel 2013 and later) and then include that in the PivotTable.
Choosing the Right Template: Built-In vs. Custom
Microsoft Excel includes several timesheet templates under File > New. Search for “time sheet” and you’ll find daily, weekly, and monthly options, often with pre-built formulas for totals and overtime. These are great if you want something professional-looking without building from scratch. However, they rarely have a “Job” column—they are usually employee-centric. To track multiple jobs, you must add that column and adjust the summary formulas. If you need a multi-project tracker, a custom sheet or a template from a reputable third-party site (like Vertex42 or Smartsheet’s free templates) may be better. Always check that the template uses standard Excel functions and doesn’t contain macros unless you trust the source.
Common Problems and How to Solve Them
Several issues frequently arise with timesheets:
- Time calculations showing as decimals like 0.5 instead of 30 minutes – This is correct if you want decimal hours for payroll (0.5 = 30 minutes). To display as “8:30”, change the cell format to [h]:mm, but then you can’t easily multiply by hourly rate. Keep decimal hours for math.
- Overnight shifts – If an employee works from 11 PM to 7 AM, a simple
(E2-D2)returns a negative. Use=IF(E2to handle crossing midnight. - Accidentally overwriting formulas – Protect the sheet as described earlier, or use data validation to restrict input to specific columns.
- Multiple employees in one file – Add an “Employee” column and then use a PivotTable to filter by person and job.
Another common mistake is entering time as text (e.g., “9:30” without AM/PM). Always use Excel’s time format (Ctrl+Shift+3 on Windows, Cmd+Shift+3 on Mac) or type “9:30 AM” so calculations work.
Comparing Excel to Dedicated Time-Tracking Software
Excel is powerful but has limits. Dedicated tools like Toggl, Harvest, or Clockify offer one-click timers, automatic idle detection, and integrations with invoicing and payroll. They also allow real-time collaboration and mobile apps. Excel, on the other hand, gives you complete control over layout and calculations, works offline, and has no per-user cost if you already own Office. For a solo freelancer or a small team with simple needs, Excel is often sufficient. For larger teams, remote work, or client billing that requires approvals, a dedicated tool saves time. You can also export data from these tools to Excel for reporting, so you’re not locked out of spreadsheet analysis.
Costs and Safety Considerations
Excel templates are free if you have Microsoft 365 (subscription) or a one-time purchase of Office. Free alternatives include Google Sheets, which offers similar functionality with cloud sharing. If you use a downloaded template, be cautious: macros can contain harmful code. Only enable macros if you trust the source, and always scan the file with antivirus software. For sensitive data, avoid storing timesheets on shared public folders; use password-protected workbooks (File > Info > Protect Workbook) or store them in a secure cloud drive with permission controls. Also, keep backups—use File > Save As to create a versioned copy each week.
Frequently Asked Questions
How do I calculate total hours for a specific job in Excel?
Use the SUMIF function. For example, with job names in B2:B100 and hours in F2:F100, enter =SUMIF(B2:B100,"Job A",F2:F100) to get total hours for “Job A”. Replace “Job A” with a cell reference like J1 if you want a drop-down selector.
Can I track billable vs. non-billable hours in the same sheet?
Yes. Add a column “Billable” with values “Yes” or “No”. Then use SUMIFS: =SUMIFS(F2:F100,B2:B100,"Job A",G2:G100,"Yes") to sum only billable hours for that job. The layout assumes G is the Billable column.
What is the best way to handle overtime in a timesheet?
Add a column “Overtime” and use a formula that subtracts 8 from daily hours if over 8: =IF(F2>8,F2-8,0). Then sum that column separately. For weekly overtime over 40 hours, use a helper column with cumulative weekly totals, but that’s more complex—consider a PivotTable with weeks.
Does Excel have a built-in timesheet template?
Yes. Open Excel, go to File > New, and search for “time sheet”. You’ll find several options, including weekly and monthly. They are free to use, but you may need to add a Job column for project tracking.
How do I prevent someone from accidentally changing formulas in the timesheet?
Select the formula cells, press Ctrl+1 (Mac: Cmd+1), go to Protection, and check “Locked”. Then go to Review > Protect Sheet and set a password (optional). Users can still enter data in unlocked cells but cannot modify formulas.
Can I use Excel timesheets on my phone?
Yes, with the Excel mobile app (iOS/Android). You can edit the same file stored in OneDrive or SharePoint. However, complex formulas and PivotTables are harder to manage on a small screen, so it’s best for quick data entry.
Related
- Excel Sales Tracking Template by Customer: How to Build One
- Excel Sales Tracking Template by Product: How to Build One
- Excel Reverse Lookup with Multiple Criteria: How-To
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Google Sheets Merge Cells Shortcut and Other MethodsGoogle Sheets has no dedicated default keyboard shortcut that merges cells directly. In a browser, press Alt+/ on…
- Microsoft Excel Employee Schedule Template: Setup TipsA Microsoft Excel employee schedule template is a reusable workbook for assigning shifts, tracking coverage, and totaling scheduled…
- Protect Excel Sheets in Teams: What It Does and HowYes. You can protect a worksheet in an Excel workbook shared through Microsoft Teams by opening the workbook…
- Rows Excel Shortcut: Insert, Delete, and Select RowsIn Excel for Microsoft 365 on Windows, select a whole row and press Ctrl+Shift+Plus to insert a new…
- Highlight Duplicates in Excel Lists: Step-by-StepTo highlight repeated values in an Excel list, select the cells you want to check, then choose Home…
- How to Insert a Citation in Microsoft Word CorrectlyTo insert a citation in Microsoft Word, place the cursor where the source should appear, open References >…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.