Excel Time Tracking Template with Formulas: How to Build One

Formulas & functions · Updated November 22, 2026

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]:mm to 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*B2 where 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:

  1. In row 1, enter: Date (A), Task/Project (B), Start Time (C), End Time (D), Hours (E), Notes (F).
  2. Select E2 and enter =IF(D2>C2, D2-C2, D2-C2+1). Press Enter, then drag the fill handle down to row 50.
  3. 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.
  4. In E52, enter =SUM(E2:E50). That cell will show total hours for the week.
  5. Optional: add a column G for hourly rate, and H for =E2*24*G2 to 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

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.