The fastest way to get an Excel time tracking template with formulas is to build a simple weekly sheet yourself: columns for Date, Task, Start Time, End Time, and a formula like =IF(D2>C2, D2-C2, D2-C2+1) to calculate hours worked, then a SUM at the bottom. This gives you a working tracker in under five minutes, and you can extend it with overtime, breaks, or project codes as needed. Below is the full breakdown of how these templates work, what formulas matter, and how to choose or build one that fits your workflow.
Why Use a Formula-Based Template Instead of a Prebuilt App
Dedicated time tracking apps (Toggl, Clockify, Harvest) are powerful, but they lock your data in a proprietary format and often require a subscription for reporting. An Excel template keeps everything on your machine, costs nothing beyond Excel itself, and lets you customize columns, formatting, and calculations without learning a new interface. The trade-off is that you maintain it yourself: formulas need to be correct, and you must be disciplined about entering data. For freelancers, small teams, or anyone who wants a lightweight record for invoices or payroll, Excel is often the most direct tool.
Core Formulas Every Time Tracker Needs
Assuming your sheet has headers in row 1 and data starting in row 2, with Start Time in column C and End Time in column D, the essential formulas are:
- Duration (hours):
=IF(D2>C2, D2-C2, D2-C2+1)– this handles overnight shifts (e.g., 10 PM to 2 AM). Format the result cell as[h]:mmto show hours over 24. - Total for the week:
=SUM(E2:E50)in a cell below the data, where column E holds your durations. - Overtime (if daily threshold is 8h):
=IF(E2>8/24, E2-8/24, 0)– note that 8 hours is stored as 8/24 because Excel treats time as fractions of a day. - Billable amount:
=E2*24*B2where B2 is your hourly rate – multiply by 24 to convert the time fraction into decimal hours.
For a worked example: if Start is 9:00 AM (C2) and End is 5:30 PM (D2), the formula returns 0.3541667 (which is 8.5 hours when multiplied by 24). Formatting E2 as [h]:mm will show 8:30. The SUM then adds all durations correctly, even if some exceed 24 hours in a week.
Building a Simple Weekly Template Step by Step
Open a blank workbook and do the following:
- In row 1, enter: Date (A), Task/Project (B), Start Time (C), End Time (D), Hours (E), Notes (F).
- Select E2 and enter
=IF(D2>C2, D2-C2, D2-C2+1). Press Enter, then drag the fill handle down to row 50. - Format C:D as
h:mm AM/PM(Home > Number Format > Time). Format E as[h]:mm– right-click E, Format Cells, Custom, type[h]:mm. - In E52, enter
=SUM(E2:E50). That cell will show total hours for the week. - Optional: add a column G for hourly rate, and H for
=E2*24*G2to get dollar amounts.
On Mac, the steps are identical; the ribbon paths are the same. In Excel for the web, the custom format [h]:mm works, but you must enter it via Format Cells > Custom (right-click the cell).
How to Handle Breaks, Overtime, and Multiple Projects
Most real-world tracking needs more than a start and end time. For breaks, add a column for Break Duration (in minutes) and subtract it: =IF(D2>C2, D2-C2, D2-C2+1) - B2/1440 where B2 holds break minutes (1440 minutes per day). For overtime, you might want weekly overtime: after the SUM cell (say E52), use =IF(E52>40/24, E52-40/24, 0) to calculate hours over 40. For multiple projects, use a PivotTable: select your data range (A1:F50), insert a PivotTable (Insert > PivotTable), drag Task to Rows and Hours to Values, then set the value field to Sum. This groups your totals by project without writing a single formula.
Common Mistakes and How to Avoid Them
Three errors trip up most people. First, using =D2-C2 directly for overnight shifts gives a negative number – the IF formula above fixes that. Second, formatting duration cells as regular time (e.g., h:mm) will display 30 hours as 6:00 because it wraps at 24; always use [h]:mm for totals. Third, entering times as text (like “9am” instead of 9:00 AM) breaks every calculation – always type times with a colon and use the time format. Also, if you copy formulas down before entering data, blank rows will show 0:00; that’s normal. To hide them, use =IF(OR(C2="",D2=""),"",IF(D2>C2, D2-C2, D2-C2+1)).
Comparing Excel Templates to Google Sheets and Paid Tools
Google Sheets offers the same core formulas (they use identical syntax), but the custom time format is slightly different: you type [h]:mm into Format > Number > Custom number format. The advantage of Sheets is real-time collaboration and automatic cloud saving. Excel’s advantage is offline power, more advanced functions like LET or dynamic arrays, and better charting. Paid tools like Toggl Track give you one-click timers, idle detection, and automatic reports, but they cost $9–$18 per user per month. If you only need a weekly log for yourself, Excel is free (if you already have Office) and fully sufficient. If you need a timer that runs in the background or team approvals, a dedicated app is worth the money.
How to Choose or Download a Prebuilt Template
Microsoft offers a free “Time Card” template in Excel (File > New, search “time card”) that includes weekly totals and overtime formulas. You can also find dozens of free templates on sites like Vertex42 or Smartsheet, but verify they use the [h]:mm format and the IF formula for overnight shifts – many free templates have bugs. When you download one, open it and test with a known time pair (e.g., 9:00 to 17:00) to confirm the total shows 8:00. If you prefer to build your own, the steps above are all you need; a custom sheet also lets you add drop-down lists for tasks (Data > Data Validation > List) and conditional formatting to highlight days over 8 hours (Home > Conditional Formatting > New Rule > Use a formula, enter =E2>8/24).
Frequently Asked Questions
Can I track time in decimal hours instead of h:mm? Yes. Change the formula to =IF(D2>C2, (D2-C2)*24, (D2-C2+1)*24) and format the result as a number with 2 decimals. Then 8.5 means 8 hours 30 minutes. This is easier for billing but less readable on screen.
Why does my total show 0:00 even though I have data? The SUM cell is probably formatted as a regular time, so it wraps at 24 hours. Right-click the total cell, choose Format Cells, Custom, and type [h]:mm. This shows cumulative hours beyond 24.
How do I subtract a lunch break automatically? Add a column for break minutes and use =IF(D2>C2, D2-C2, D2-C2+1) - B2/1440, where B2 is the break in minutes. For a 30-minute lunch, enter 30 in that column.
Does this work on Excel for Mac? Yes, all formulas and formatting steps are identical. The only difference is that some keyboard shortcuts use Cmd instead of Ctrl (e.g., Cmd+1 for Format Cells).
Can I use this for payroll or legal purposes? Excel can produce an accurate record, but it is not a certified payroll system. For legal compliance, keep the original file with timestamps, and verify your local labor laws regarding overtime and rounding. Many companies require a separate payroll software for official records.
What if I need to track time for multiple employees? Add a column for Employee Name, then use a PivotTable (Insert > PivotTable) with Employee in Rows and Hours in Values to get per-person totals. For a live view, use =SUMIF(A:A, "John", E:E) where A holds employee names and E holds hours.
Related
- Excel Print Merge File Showing X? Fixes
- Excel Profit and Loss Template: Free Options
- Excel Power Query Wrap Text: How to Do It
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- SUMIFS in Excel: Multiple Criteria, Examples and TipsYes. In Excel, SUMIFS adds numbers only when multiple conditions are met; each criteria pair can test a…
- Import CSV Into Excel as Rows, Not Columns the Right WayTo import a CSV into Excel with each record on its own row, open Excel and choose Data…
- ISERROR in Excel: Check for Errors in Your FormulasIn Excel, use ISERROR to check whether a value or calculation produces an error: enter a formula such…
- Excel Formula to Build a Table AutomaticallyYes—Excel formulas can build a changing, table-shaped result, but a formula cannot create an actual Excel Table object.…
- Excel A to Z Formula: How to Sort DataWith the values to sort in A2:A20 and an empty output area beginning at C2, enter =SORT(A2:A20) in…
- How to Sort Alphabetically in Excel Without Mixing RowsTo sort alphabetically in Excel for Microsoft 365 on Windows, select a cell in the column, then choose…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.