Excel Time Tracker Formula: How to Build One

Formulas & functions · Updated November 22, 2026

The core Excel time tracker formula is =End Time - Start Time, formatted as [h]:mm to show total hours beyond 24. For example, with start time in B2 and end time in C2, enter =C2-B2 in D2, then apply the number format [h]:mm to get elapsed hours. This article explains how to build a complete time tracking spreadsheet, handle overnight shifts, calculate pay, and avoid common errors.

Why Use Excel for Time Tracking

Excel is a flexible, low-cost alternative to dedicated time tracking apps. You can customize columns for tasks, projects, or clients, and use built-in functions to sum hours, compute overtime, or generate reports. Unlike paper timesheets, Excel formulas automatically calculate totals, reducing manual errors. It also works offline and requires no subscription, making it ideal for freelancers, small businesses, or personal productivity tracking.

Setting Up Your Time Tracker Spreadsheet

Start with a simple layout. In row 1, create headers: Date, Start Time, End Time, Break (minutes), and Total Hours. For example, put dates in A2:A10, start times in B2:B10, end times in C2:C10, and break minutes in D2:D10. In E2, enter the formula =C2-B2-D2/1440 – dividing break minutes by 1440 converts minutes to days (since Excel stores time as fractions of a day). Format E2 as [h]:mm to display hours and minutes. Copy the formula down for all rows.

Handling Overnight Shifts and Negative Times

A common issue: if an employee starts at 10 PM and ends at 6 AM, the simple subtraction =C2-B2 returns a negative value. To fix this, use the MOD function: =MOD(C2-B2,1). This wraps around midnight and returns the correct positive elapsed time. For example, with B2=22:00 and C2=06:00, =MOD(C2-B2,1) gives 0.3333, which formats as 08:00. Alternatively, you can add a date column and use full date-time values, but MOD is simpler for daily tracking.

Calculating Total Hours and Pay

To sum all daily hours, use =SUM(E2:E10) in a Total Hours cell. Format it as [h]:mm to show cumulative hours beyond 24 (e.g., 40:30). For pay calculations, multiply total hours by an hourly rate. If rate is in G1, use =SUM(E2:E10)*24*G1 – multiplying by 24 converts the time value to decimal hours. For example, with 40:30 total and $20/hour, the formula returns $810.00. For overtime, add a column for regular hours and overtime hours, using formulas like =MIN(E2,8) and =MAX(E2-8,0) for an 8-hour day.

Using PivotTables for Project or Client Reports

If you track multiple projects, add a Project column (e.g., F). Then create a PivotTable: select your data range, go to Insert > PivotTable, and place Project in Rows and Total Hours in Values. Set the Value Field Settings to Sum and number format to [h]:mm. This gives you a quick summary of hours per project. For client billing, you can also add a Rate column and multiply hours by rate inside the PivotTable using a calculated field, though it’s often easier to do this with a formula in the source data.

Common Formula Errors and How to Fix Them

  • Negative time (####): Use =MOD(C2-B2,1) for overnight shifts.
  • Wrong format: Apply [h]:mm to time cells, not h:mm, to allow totals over 24 hours.
  • Text entries: Ensure times are entered as actual times (e.g., 9:00 AM), not text. Use Data Validation to restrict input to time format.
  • Break subtraction: Always divide minutes by 1440 to convert to days.
  • Decimal vs. time: Remember that Excel stores times as fractions of a day; multiply by 24 to get decimal hours.

Comparing Excel Time Trackers to Dedicated Apps

Excel gives you full control and no recurring cost, but it lacks automatic reminders, mobile apps, and integration with payroll systems. Dedicated apps like Toggl or Harvest offer one-click timers and reporting, but require a subscription and internet. For simple tracking, Excel is sufficient. For complex needs like GPS tracking or team collaboration, consider a dedicated tool. You can also export Excel data to CSV and import into other software if needed.

Frequently Asked Questions

How do I calculate hours worked in Excel including breaks?

Use the formula =MOD(End-Start,1)-Break/1440, where Break is in minutes. For example, start 9:00, end 17:30, break 30 minutes: =MOD(17:30-9:00,1)-30/1440 returns 8:00. Format the result as [h]:mm.

Can I track time automatically with a formula?

Yes, you can use the NOW() function to capture the current time. For example, enter =NOW() in a cell when you start a task, and another =NOW() when you finish, then subtract. However, the time updates only when the sheet recalculates, so you must press F9 or save to capture a snapshot. For automatic timestamps without VBA, consider using a checkbox or a simple macro.

Why does my time total show as 0:00 or a small number?

This usually happens when the cell is formatted as h:mm instead of [h]:mm. The h format resets to zero after 24 hours, so a total of 30 hours displays as 6:00. Change the number format to [h]:mm to show cumulative hours.

How do I track time for multiple employees in one sheet?

Add columns for Employee Name and Date, then use a PivotTable to summarize hours per employee. Alternatively, use separate sheets per employee and link totals with formulas like =SUM(Sheet2!E:E).

Is there a way to round time to the nearest 15 minutes?

Yes, use the MROUND function: =MROUND(C2-B2,

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.