To track time in Excel, create columns for Date, Start Time, End Time, and Break, then use the formula =(End-Start)*24-Break/60 to calculate total hours worked as a decimal number.
That is genuinely all you need for a basic timesheet. Excel is free if you already have it, flexible enough for most use cases, and familiar to anyone who has used a spreadsheet. You can have a working time tracker in 15 minutes without installing anything. This guide covers the setup, the formulas, and the point at which Excel stops being enough.
How to Create a Time Tracker in Excel
Start with five columns: Date, Start Time, End Time, Break (min), and Total Hours. Add optional columns for Project, Task, and Billable (Yes/No) if you need to track where your time goes, not just how much you work.
Format your time cells correctly
This is where most people get stuck. Excel stores time as a fraction of a day. One hour equals 1/24, or 0.041667. When you enter “9:00 AM,” Excel stores it as 0.375 (nine twenty-fourths of a day).
This matters because your Total Hours column needs to display hours as a number, not as a time. If you subtract 8:00 AM from 5:00 PM, Excel returns 0.375 — which it displays as “9:00” in time format. That looks right, but it breaks the moment you try to sum a week’s hours or multiply by an hourly rate.
Format your Total Hours column as Number (two decimal places), not as Time. This gives you 9.00 instead of 9:00, which sums correctly and works in pay calculations.
The core formula
For each row, calculate total hours with:
=IF(AND(B2<>"",C2<>""),(C2-B2)*24-D2/60,"")Where B2 is Start Time, C2 is End Time, and D2 is Break in minutes. The *24 converts from Excel’s fractional-day format to decimal hours. The -D2/60 converts break minutes to hours and subtracts them.
Example layout
| Date | Start Time | End Time | Break (min) | Total Hours | Project | Billable? |
|---|---|---|---|---|---|---|
| 17/03/2026 | 09:00 | 12:30 | 15 | 3.25 | Client A | Yes |
| 17/03/2026 | 13:30 | 17:00 | 0 | 3.50 | Internal | No |
This two-entry day totals 6.75 hours, of which 3.25 are billable. Simple, readable, and accurate.
Essential Excel Formulas for Tracking Time
Once you have the basic timesheet, a handful of formulas turn it from a log into a useful reporting tool.
How to subtract time in Excel
The basic subtraction is =C2-B2, but there are two traps to watch for.
Trap 1: Overnight shifts. If End Time is earlier than Start Time (a shift crossing midnight), standard subtraction returns a negative number. Fix it with:
=IF(C2>=B2,(C2-B2)*24,(1-B2+C2)*24)Trap 2: The [h]:mm format. If you want totals displayed as hours and minutes rather than decimals, use Custom format [h]:mm. The square brackets are critical — without them, Excel wraps at 24 hours and a 42-hour week displays as 18:00.
Summing hours by project
Use SUMIF to aggregate hours by project or client:
=SUMIF(F2:F100,"Client A",E2:E100)This returns total hours for every row where the Project column matches “Client A.” For billable vs non-billable hours, add a Billable column and use:
=SUMIF(G2:G100,"Yes",E2:E100)Overtime calculation
If the standard working day is 8 hours, calculate daily overtime per row:
=MAX(0,E2-8)For weekly overtime above 40 hours, sum the week first, then:
=MAX(0,SUM(E2:E6)-40)As one Excel tutorial practitioner demonstrates, you can then split pay into regular and overtime rates: =MIN(TotalHours,40)*Rate + MAX(0,TotalHours-40)*Rate*1.5. This gives you accurate weekly pay with time-and-a-half overtime in a single formula.
Pay calculation
For basic pay: =E2*HourlyRate. For a weekly pay summary with overtime:
Regular pay: =MIN(H17,40)*PayRate
Overtime pay: =MAX(0,H17-40)*PayRate*1.5
Total pay: =RegularPay+OvertimePayUse named ranges or a separate rates table rather than hard-coding numbers into formulas. This makes it easy to update rates without editing every cell.
How to Track Time Spent on Tasks in Excel
Basic clock-in/clock-out tracking tells you how much you work. Task-level tracking tells you where that time goes — which is far more useful for billing clients and managing projects.
Use data validation for consistent naming
Add a Task or Project column and apply Data Validation (dropdown list) to prevent inconsistent entries. Without this, “Client A,” “client a,” and “ClientA” show up as three different projects in your reports. One tutorial practitioner demonstrated using SUMIF across validated columns to create automatic per-client totals that update as you add entries.
Build a dashboard with Pivot Tables
Once you have a month of time data, insert a Pivot Table (Insert > PivotTable). Drag Date to Rows, Project to Columns, and Total Hours to Values. Immediately you can see which projects consumed the most time, which weeks were heaviest, and how your allocation shifts over time.
Add a Timeline slicer (Insert > Timeline) to filter by week or month interactively. This turns a flat spreadsheet into a reporting tool that answers questions like “how much time did I spend on Client B in February?”
Conditional formatting for quick visibility
Apply colour scales to the Total Hours column: green for normal days, amber for long days, red for days exceeding 10 hours. Add rules to highlight rows with zero break time. These visual cues surface problems — overwork, missed breaks, unrecorded days — without requiring manual review.
Free Excel Timesheet Templates
You do not need to build your timesheet from scratch. Several free templates cover common use cases.
Weekly timesheet — seven-day layout with start/end times, breaks, project columns, and auto-calculated daily and weekly totals. Includes a billable/non-billable split row. Best for freelancers and consultants who bill weekly.
Monthly timesheet — calendar-style layout with one row per day, project allocation columns, and a monthly summary section. Includes overtime and leave tracking. Best for employees and managers who report monthly.
Project timesheet — organised by project rather than by date. Each sheet tracks a single project with task breakdowns, team member columns, and budget-vs-actual hours comparison. Best for project managers tracking team utilisation rates.
Where to find them: search “timesheet” in Excel’s built-in template gallery (File > New > Search). For more advanced templates with formulas pre-built, free spreadsheet resource sites offer downloadable weekly and monthly timesheets. One automation-focused tutorial showed how to use a simple VBA macro to generate 52 weekly timesheet tabs from a single template — useful for creating a full year of tracking in seconds.
When Does Excel Time Tracking Break Down?
Excel is a valid time tracking tool for individuals and small teams. But it has hard limits, and it is worth knowing where they are before you hit them.
Five or more people. When multiple people submit timesheets, you end up consolidating dozens of files. Version control breaks down — which file has the latest entries? Manual merging is error-prone. At this point, a shared time tracking tool saves more time than the spreadsheet costs.
Live tracking. Excel logs time after the fact. You cannot run a timer in a standard spreadsheet. Some VBA-based templates add timer buttons, but they are fragile and do not sync across devices.
Mobile entry. Field workers and remote employees need to log time from their phones. Excel on mobile is functional but clunky. Dedicated time tracking apps are designed for quick, on-the-go input.
Integrations. If time data needs to flow into invoicing, payroll, or project management tools, Excel requires manual export and import. Dedicated software integrates directly with accounting and billing platforms.
AI agent time. Excel cannot track time for AI agents working alongside your team. As businesses deploy AI for research, coding, and data analysis, they need unified tracking across human and agent hours — something spreadsheets were never designed for. This is where AI time tracking picks up where Excel leaves off.
The upgrade path
- Free tier tools — many dedicated platforms offer free plans for small teams (2–5 users) with timers, project tracking, basic reporting, and mobile apps.
- Paid tools (£5–15/user/month) — add invoicing integration, team dashboards, approval workflows, and advanced reporting.
- AI-native tracking — platforms that track both human hours and AI agent activity on the same dashboard, giving you unified project costing across your entire workforce.
Migration is straightforward: export your Excel data as CSV, import into the new tool, and your historical data comes with you. Most dedicated tools also offer Excel export, so you keep spreadsheet-level flexibility for ad hoc analysis.
Key Takeaway
Excel handles time tracking well for individuals and tiny teams. Use
=(End-Start)*24for hours, SUMIF for project totals, and Pivot Tables for reporting. Upgrade when you hit five people, need live timers, or work with AI agents.
Frequently Asked Questions
How do you track time in Excel?
Create columns for Date, Start Time, End Time, Break, and Total Hours. Use the formula =(C2-B2)*24-D2/60 to calculate decimal hours per entry. Format Start and End as Time, and Total Hours as Number with two decimal places.
What is the formula for calculating hours worked in Excel?
=(End Time - Start Time)*24 converts the time difference from Excel’s fractional-day format to decimal hours. Subtract break time in minutes divided by 60. For example: =(C2-B2)*24-D2/60 where D2 is break minutes.
How do you subtract time in Excel?
Use =C2-B2 for same-day entries. For overnight shifts where the end time is earlier than the start, use =IF(C2>=B2,C2-B2,1-B2+C2) to handle the midnight crossover correctly.
How do you create a timesheet template in Excel?
Set up columns for Date, Start Time, End Time, Break, Total Hours, Project, and Billable. Apply time formatting to input columns, number formatting to Total Hours. Add SUMIF formulas for per-project totals and a SUM row for daily/weekly totals. Save as a template (.xltx) for reuse.
How do you track time spent on tasks in Excel?
Add a Task or Project column with data validation (dropdown lists) for consistent naming. Use =SUMIF(ProjectColumn,"TaskName",HoursColumn) to sum hours per task. For visual analysis, insert a Pivot Table with Date on rows, Project on columns, and Total Hours as values.
Can you track billable hours in Excel?
Yes. Add a Billable column (Yes/No) and use =SUMIF(BillableColumn,"Yes",HoursColumn) for total billable hours. Calculate your billable percentage with =BillableHours/TotalHours*100. Multiply billable hours by your rate for invoice amounts.
When should you stop using Excel for time tracking?
When your team exceeds five people, when you need real-time timers, when mobile entry is required, when time data must integrate with payroll or invoicing, or when AI agents work alongside your team and need their time tracked on the same platform.
How do you handle overnight shifts in Excel time tracking?
Standard subtraction returns a negative number for shifts crossing midnight. Use the formula =IF(C2>=B2,(C2-B2)*24,(1-B2+C2)*24) which adds a full day when the end time is earlier than the start time, then multiplies by 24 to convert to decimal hours.