Excel Time Tracker: Build One That Adds Up

How-to basics · Updated November 18, 2026

An Excel time tracker is a spreadsheet for recording when work starts and ends, subtracting breaks, and totaling hours by day, project, or week. In Microsoft Excel for Microsoft 365 on Windows, a basic version needs a date, start time, end time, break length, and a duration formula; format duration totals as [h]:mm so they keep adding correctly beyond 24 hours.

What an Excel time tracker records

A tracker can be as simple as a personal work log or as detailed as a project timesheet. Its purpose is to turn individual time entries into consistent, reviewable totals. Before building one, decide what question the sheet should answer: How long did a shift take? How many hours went to each client? Which tasks consumed the most time this month? The answer determines which columns and summaries you need.

For a straightforward daily log, useful columns include:

  • Date: the day the work took place.
  • Start and end: the times the work period began and finished.
  • Break: the unpaid or otherwise excluded break length, if applicable.
  • Project or task: a consistent label that supports later summaries.
  • Net time: elapsed time after subtracting the break.
  • Notes: optional context, such as a task description or correction reason.

One row per work period is usually easier to audit than one row per day. If you worked on three clients during a shift, record three intervals and assign each one a project. If you only need a personal estimate of daily hours, one row per day may be sufficient. The right level of detail is the least complicated structure that still answers the questions you care about.

Set up the workbook

Create a worksheet and put clear column headings in row 1. For example, use A for Date, B for Start, C for End, D for Break (minutes), E for Net time, F for Project, and G for Notes. Enter one work interval per row, beginning in row 2. Keep the source entries separate from calculated totals: it is easier to check a result when the original start, end, and break values remain visible.

Format the date column as a date and the start and end columns as times. Select the relevant cells, press Ctrl+1 on Windows, and use the Number tab to choose the appropriate Date or Time format. On a Mac, Command+1 opens Format Cells. The precise display can vary by regional settings, but a displayed value such as 9:00 AM is still a time value Excel can calculate with.

For a reusable log, select the headings and a row of data, then choose Insert > Table and confirm that the table has headers. A table makes it easier to filter by project or date and generally extends formatting and calculated-column formulas as new rows are added. It does not replace checking entries: a blank end time or a typo can still produce an incorrect result.

Calculate net hours and format the result

With the example layout above, where the start is in B2, the end is in C2, and break length in minutes is in D2, enter this in E2 for a same-day shift:

=MOD(C2-B2,1)-D2/1440

The formula treats Excel times as fractions of a day. Dividing break minutes by 1,440 converts them to a fraction of a day, because a day contains 1,440 minutes. For a 9:00 AM start, 5:30 PM finish, and 30-minute break, the result is 8:00. Copy the formula down the column, or enter it in a table calculated column so it can fill for new rows.

Format E2 and the rest of the Net time column using Ctrl+1 on Windows, choose Custom, and enter [h]:mm as the format code. On Mac, use Command+1 to open Format Cells. The square brackets tell Excel to show cumulative elapsed hours rather than wrapping the display at 24 hours. Without them, a weekly total of 40 hours can appear as 16:00, even though the underlying value has not changed. The format changes how a value is displayed, not the value itself.

This example assumes each row describes a single work interval lasting less than 24 hours and that the break is entered as a number of minutes. If breaks are entered as Excel time values instead, the formula needs to subtract that time value directly rather than divide minutes by 1,440. Pick one convention, label the column clearly, and use it consistently.

Handle overnight shifts and unusual entries

The MOD function in the example allows the end time to be earlier on the clock than the start time. For example, a 10:00 PM start and a 6:00 AM finish produces eight hours before any break is deducted. This is useful for ordinary overnight shifts, but it assumes the end time belongs to the next day when it is earlier than the start. It cannot distinguish a genuine overnight shift from an accidental entry that has the wrong time.

For shifts that may span a full day or more, record complete date-and-time values for both the start and finish instead of times alone. With the start date and time in B2, the end date and time in C2, and break minutes in D2, use:

=C2-B2-D2/1440

For example, if B2 is November 2 at 10:00 PM and C2 is November 3 at 6:30 AM, with a 30-minute break in D2, the elapsed net result is 8:00. Format the result as [h]:mm. Entering full date-and-time values avoids relying on the assumption that an earlier clock time always means the next calendar day.

Other safeguards help catch mistakes before totals are trusted. Use Data > Data Validation to restrict entries where appropriate, such as requiring a break-minute value to be a nonnegative whole number. Review rows with missing dates or times, breaks longer than the shift, and implausibly long durations. A spreadsheet can calculate exactly what it is given; it cannot tell whether an entry reflects what actually happened.

Total hours by week or project

To total a week, add the net-time cells for that period. With Monday through Friday entries in E2:E6, enter:

=SUM(E2:E6)

If each of those five entries is 8:00, the total is 40:00 when the result cell uses the [h]:mm format. If the result displays a clock time such as 16:00, check the format before assuming the arithmetic is wrong. Make sure the selected range also includes every relevant row and excludes unrelated entries.

Some readers prefer decimal hours for reporting. With a net duration in E2, use:

=E2*24

An 8:00 duration returns 8. Format the result as a number, perhaps with two decimal places, if you want a display such as 8.00. Do not add decimal-hour values to time-formatted values without converting them consistently: 8.5 decimal hours means eight hours and 30 minutes, while Excel’s time value for that duration is a fraction of a day.

Project totals require a reliable project label on every applicable row. You can filter the table to one project and inspect its net-time values, or build a PivotTable from the logged data and summarize Net time by Project and Date. In a PivotTable, check the value-field number format after summarizing; a duration total should be displayed as elapsed hours, not as a time of day. Keeping project names consistent matters: entries labelled Design, design, and Design work may be treated as different categories.

Make the tracker easier to use and review

A tracker is more useful when it is quick to fill in and easy to check. Keep headings specific, freeze the top row if the log is long, and filter out dates or projects that are not relevant to a review. A short list of standard project names reduces spelling variations. If several people enter data, explain whether break length is entered in minutes, whether times use a 12- or 24-hour display, and how corrections should be noted.

Consider these practical habits:

  • Enter time as a time value, not text; if a value is left-aligned or calculations fail, check whether Excel interpreted it as text.
  • Use one row per distinct work period when work must be assigned to different tasks or projects.
  • Keep the original entries and formulas together, and avoid typing over calculated cells.
  • Check totals against the underlying entries before sharing or using them for a decision.
  • Save a separate copy before making major structural changes, and keep backups of records that matter.

For team use, protect formula cells if accidental edits are a concern, while leaving input cells available to the people who need them. Worksheet protection can prevent some edits, but it is not a substitute for access controls or secure storage. Do not put sensitive client or employee details in a broadly shared workbook unless its location and permissions are appropriate.

When Excel is enough—and when it is not

Excel works well for a small, low-complexity log when the people entering time understand the format and someone can review the results. It is flexible, familiar to many office users, and easy to adapt with columns for projects, notes, or approvals. A spreadsheet also makes the underlying entries visible, which can help a person understand how a total was calculated.

Its limitations become more important as the process grows. A workbook does not automatically verify that a person worked the recorded hours, enforce a reliable approval process, or resolve conflicting edits. Multiple copies can diverge, and formula cells can be overwritten. Shared workbooks and cloud storage may support collaboration, but they do not by themselves provide every audit, scheduling, or policy feature an organization might require.

Dedicated time-tracking software may be more suitable when a team needs individual accounts, reminders, approval workflows, mobile entry, detailed audit history, or integrations with invoicing and payroll systems. Check current product features, pricing, data handling, and export options before choosing one; these vary by provider and plan. For a simple personal record, Excel may be easier to maintain than a specialized system. For formal payroll, billing, or compliance records, check the applicable organizational requirements and have the process reviewed by an appropriate professional rather than assuming a spreadsheet meets them.

Frequently Asked Questions

How do I calculate hours worked in Excel?
Subtract the start time from the end time, then subtract any break. For a single shift under 24 hours with start in B2, end in C2, and break minutes in D2, =MOD(C2-B2,1)-D2/1440 calculates net time. Format the result as [h]:mm.

Why does my total show the wrong time after 24 hours?
Excel may be displaying a duration with a clock-time format that wraps after a day. Select the total cell, open Format Cells, and apply the custom format [h]:mm. The brackets show accumulated hours instead of resetting the display at 24.

Can Excel calculate an overnight shift?
Yes. The MOD formula can handle a shift such as 10:00 PM to 6:00 AM if the interval is under 24 hours and the end is on the following day. For longer shifts or records where dates matter, enter full start and end date-and-time values and subtract them directly.

Should I record breaks in minutes or as times?
Either method can work, but the formula must match the chosen input. The examples here use a number of minutes, such as 30, and divide it by 1,440 to convert it to a time fraction. Label the column clearly so another person does not mistake 30 minutes for an Excel time value.

Can I use an Excel time tracker for payroll?
A workbook can calculate recorded durations, but accurate arithmetic alone does not establish that the record meets payroll, company, or legal requirements. Rules and procedures depend on the situation and location. Confirm the requirements that apply to you and use suitable review and recordkeeping procedures.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

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

Spotted an error in this guide? Let us know.