An employee time tracking template in Excel is a worksheet that records each person’s work date, start time, end time, unpaid break, and hours worked. Set up columns A–I for Date, Employee, Department, Start time, End time, Unpaid break (min), Hours worked, Project, and Notes; with start and end times in D2 and E2 and break minutes in F2, enter =IF(OR(D2="",E2=""),"",MOD(E2-D2,1)*24-F2/60) in G2. The formula calculates elapsed hours, handles shifts that cross midnight, and leaves the result blank until both times are entered.
What an Excel time tracking template is for
A time tracking spreadsheet gives employees and managers a shared record of when work was performed and, when needed, which project or department the time belongs to. A straightforward template can support weekly timesheet review, project-cost estimates, scheduling discussions, or preparation of hours for another payroll or accounting process. It is a recordkeeping tool, not a payroll system: it does not automatically determine which pay rules apply or confirm that entries are correct.
Before building the sheet, decide what a row represents. For most small teams, one row per employee per shift is easy to review and summarize. If employees need to allocate a shift across several projects, use one row per employee, date, and project segment instead. In that design, an employee who works four hours on one project and four on another enters two rows, with the same date and different project values. Choose one approach and explain it to everyone; mixing both creates double-counting risk.
Choose columns that match the work
The nine suggested columns cover the basics without making every entry complicated. Date identifies the workday; Employee identifies the person; Department and Project support grouping; start, end, and break fields produce the duration; Hours worked stores the calculated result; and Notes can capture a correction or an unusual circumstance. If project allocation is irrelevant, remove that column rather than asking staff to enter meaningless data.
- Date: Enter the date the shift began. Use a consistent date format so sorting and filtering work as expected.
- Employee and Department: Use consistent names or IDs. A dropdown list can reduce spelling variations, but keep the source list current.
- Start time and End time: Enter actual clock times as Excel times, such as 8:30 AM and 5:00 PM, rather than text that only looks like a time.
- Unpaid break (min): Record the break in minutes if that is the team’s chosen method. If the organization tracks multiple breaks or paid breaks separately, add fields that reflect that policy.
- Hours worked: Calculate this field consistently rather than asking each employee to do mental arithmetic.
- Project and Notes: Keep notes brief and work-related. Avoid storing sensitive personal details in a general timesheet.
Build the worksheet in Microsoft Excel
In Excel for Microsoft 365 on Windows, type the column names in row 1 and enter one example shift in row 2. Select the range and choose Insert > Table, confirm that the table has headers, and select OK. A table makes it easier to filter by employee, date, department, or project, and it can carry a formula down as new rows are added. On Mac and in Excel for the web, the Insert tab also provides a Table command, although the ribbon layout can differ slightly.
Format the Date column as a date and the Start time and End time columns as times. Select the relevant cells, then use Home > Number to choose an appropriate format. Format Hours worked as a number with two decimal places, not as a clock time: 8.50 means eight and a half hours, whereas 8:30 is a time-of-day display. Freeze the header row if the sheet will be long, and use the table’s filter buttons to inspect a single employee or week.
Apply the hours formula in G2. With ordinary cell references, Excel tables generally fill the formula down the column; if they do not, copy it down for the rows in use. The MOD function treats the time difference as a clock interval, so a shift from 10:00 PM to 6:00 AM returns eight hours before the break is deducted. Check entries that produce a negative result: that can indicate an incorrectly entered break or a formula/input problem.
Understand the hours formula and its limits
The formula uses Excel’s stored time values, where a full day is represented by 1. Multiplying the time difference by 24 converts a fraction of a day into hours. Dividing the unpaid break minutes by 60 converts minutes into hours before subtracting them. The IF test keeps incomplete shifts blank instead of showing a misleading number while someone is still entering a row.
For example, if D2 is 8:30 AM, E2 is 5:00 PM, and F2 is 30, the elapsed shift is 8.5 hours and the unpaid break is 0.5 hours. The formula returns 8. If the shift runs from 10:00 PM to 6:00 AM with a 30-minute unpaid break, it returns 7.5. The formula assumes that a time difference that wraps past midnight represents an overnight shift; it cannot identify every unusual case from clock times alone. A shift lasting exactly 24 hours, for example, has identical start and end times and would appear as zero. Record the date and shift details clearly, or add a separate date/time field if unusually long shifts are possible.
Do not round each shift automatically unless the organization has an established, appropriate method for doing so. Rounding individual entries can change the total. Retaining the calculated precision in the cells and displaying two decimal places is different from changing the underlying value. Also, a formula cannot verify whether a break was actually taken or whether a time entry is accurate; review and approval remain important.
Summarize a week and review entries
For a simple weekly total, place a total below the relevant hours. With the week’s calculated values in G2:G8, enter =SUM(G2:G8) in G9. If those rows contain 8, 7.5, 8, 8, 6, 0, and 0 hours, the result is 37.5. This is a basic sum, so it is useful only when the selected rows represent the intended employee and period. Use table filters to isolate the right records before relying on a simple total.
When the sheet contains many employees or several weeks, a PivotTable can summarize hours by employee, department, date, or project. Select a cell in the data table and choose Insert > PivotTable, then place the fields you want to group in Rows and Hours worked in Values. Check that the Values field is summarized by Sum rather than Count. A PivotTable is a report of the recorded data; refresh it after entries change, and verify the date range and filters before sharing totals.
Set a routine for review. An employee can check the dates and hours before submitting a period; a manager can look for missing entries, unexpected durations, duplicate rows, and unexplained edits. Keep corrections traceable—for example, use a correction note or maintain a separate approval record—rather than silently overwriting disputed values. If the workbook is shared, agree who may edit the source entries and who may approve them.
Improve data quality without making the sheet hard to use
Good templates prevent common entry mistakes while remaining simple enough for regular use. A short instruction row or a separate Instructions worksheet can explain the time format, break convention, date range, and submission deadline. Use consistent employee names or IDs; two spellings for the same person may split a summary into separate records. Avoid merged cells in the data area because they make sorting, filtering, and PivotTables harder.
- Use Data > Data Validation to restrict entries where a controlled list or permitted range is appropriate, such as a list of departments.
- Use clear column headings and one header row; do not insert blank rows inside the records.
- Use conditional formatting sparingly to draw attention to missing or unusual entries, then investigate rather than treating a highlight as proof of an error.
- Keep a dated backup or a controlled copy of submitted periods so later edits do not erase the record that was reviewed.
Data validation can reduce accidental variation, but it is not a security boundary and does not establish that an entry is truthful. Likewise, protecting a worksheet can prevent some accidental changes, but it should not be treated as a substitute for access controls on the file. Store the workbook in an approved location, limit access to people who need it, and follow the organization’s retention and privacy practices.
Common problems and practical limitations
A frequent problem is entering a time as text. If Excel does not recognize a value as a time, calculations may fail or behave unexpectedly. Re-enter it using a consistent time format and check the cell’s number format. Another issue is an incorrect break convention: if one person enters 30 minutes and another enters 0.5 intending half an hour, the formula interprets 0.5 as half a minute because the column is defined in minutes. Label the field clearly and state the unit in the instructions.
Overnight shifts deserve special attention. MOD handles a normal shift that crosses midnight, but the date column should still represent the date the shift began, and the team should decide how to record hours that fall on the following date for reporting purposes. A single calculated duration does not split hours across calendar days. If that distinction matters to reporting or a separate system, the workbook needs a more detailed design.
Other limitations concern policy and scale. A spreadsheet will not automatically apply every organization’s rules for overtime, rounding, paid breaks, holidays, or corrections. Requirements vary by location and situation, so confirm applicable rules with qualified HR or legal professionals rather than assuming a template establishes compliance. For a large workforce, frequent schedule changes, approvals, or integration needs, a shared workbook can become difficult to control. Protect employee information and avoid emailing multiple conflicting copies.
When to use Excel and when to consider another tool
Excel can be a reasonable choice for a small team with a stable process, modest record volume, and someone responsible for checking the workbook. It is familiar, flexible, and can be adjusted to include project codes or notes without requiring a dedicated system. A shared workbook in an approved Microsoft 365 location can reduce version confusion compared with circulating attachments, but access and editing practices still need to be managed.
Consider dedicated timekeeping or workforce software when the process needs employee clock-in controls, manager approval flows, schedules, audit trails, payroll exports, multiple locations, or automated policy handling. Compare tools based on the actual workflow, permissions, reporting, integrations, support, and total cost—not just whether they can display a timesheet. A software tool also needs accurate setup and review; adopting one does not remove the need to understand the organization’s rules.
Frequently Asked Questions
How do I calculate hours worked in Excel from start and end times?
Subtract the start time from the end time, convert the time fraction to hours, and subtract any unpaid break. The MOD-based formula above also handles a shift crossing midnight, provided the shift is a normal overnight duration and the break is entered in minutes.
Can an Excel time tracking template handle overnight shifts?
Yes. The MOD function in the example treats an end time earlier than the start time as a shift continuing into the next day. Record the shift date consistently, and use a more detailed date-and-time design if you must allocate hours to separate calendar days.
Should employees enter hours or clock-in and clock-out times?
For a simple shift record, start and end times plus a clearly defined break field make it easier to review how the total was calculated. Directly entered hours may be quicker, but they provide less detail for checking discrepancies. Choose the method that fits the review process and applicable requirements.
How can I total hours by employee or week?
Filter the table to the employee and date range, then sum the Hours worked values, or create a PivotTable that groups records by employee and period. Confirm that the selected dates are correct and that the PivotTable summarizes hours by Sum.
Is an Excel timesheet enough for payroll or compliance?
Not necessarily. A workbook can calculate and organize recorded time, but it does not determine which pay rules apply, prove that entries are accurate, or automatically meet every recordkeeping requirement. Check the relevant process and rules with qualified professionals and use appropriate review and access controls.
Related
- Customer Details in Excel: How to Set Up a Useful List
- Customizable Calendar Template in Excel: How to Make One
- Email Merge from Excel: Steps, Setup, and Safer Sending
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Add Page 1 of X in a Word HeaderTo show a live “Page 1 of X” count in a Microsoft Word header, insert a PAGE field…
- Encrypt an Excel File: Steps, Limits, and Safer SharingTo encrypt an Excel file in Microsoft Excel for Microsoft 365 on Windows, open the workbook and choose…
- How to Clear Cache in Outlook for WindowsTo clear Outlook’s local cache in Windows, first identify which Outlook you use: in classic Outlook, clear the…
- Box and Whisker Plot in Excel: A Step-by-Step GuideTo build a box and whisker plot in Excel for Microsoft 365 on Windows, arrange each data group…
- Philippine Wedding Budget Spreadsheet: Build a BudgetA Philippine wedding budget spreadsheet is a practical way to track estimated costs, actual costs, payments, and balances…
- Create Rules in Outlook on the Web: A Step-by-Step GuideTo create a rule in Outlook on the web, open Settings, go to Mail > Rules, select Add…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.