Yes. Excel can track employee start times, finish times, unpaid breaks, and hours worked in a reusable time sheet. Set up one row per employee per workday, enter times consistently, and use a formula such as =MOD(D2-C2,1)*24-E2 to calculate hours, including an overnight shift. A spreadsheet is useful for straightforward tracking, but it still needs review before hours are used for payroll.
What an employee time sheet does
An employee time sheet is a record of when someone worked, how much time they worked, and sometimes which project, department, or type of work they supported. In Excel, it can be a simple daily log or a structured workbook that summarizes a team’s hours by week. The sheet calculates totals from entries; it does not independently confirm that those entries are correct.
A basic sheet is different from a full workforce-management system. Excel can organize hours and flag missing entries, but it does not automatically verify a person’s identity, approve a timesheet, or ensure that a pay rule is being applied correctly. Those tasks require a reliable review process, and in some workplaces dedicated timekeeping software may be more appropriate.
Choose what to record
Decide what information is necessary before building the workbook. A useful everyday layout typically includes:
- Employee name or unique employee ID.
- Work date, entered as a real Excel date rather than text.
- Start time and end time.
- Unpaid break duration, if applicable.
- Calculated hours worked.
- Optional project, location, department, notes, and approval status.
Keep the information proportional to the task. If supervisors only need weekly hours, a project-code field may be unnecessary. If employees divide time across client work, project codes may be essential. Avoid collecting sensitive personal details that are not needed for timekeeping. For a larger team, an employee ID can help distinguish people who share a name.
Set up the workbook
In Excel for Microsoft 365 on Windows, create headers in row 1, for example: Employee, Date, Start, End, Break (hours), and Hours worked. Put one employee’s workday on each row. Select the range and choose Insert > Table, then confirm that the table has headers. Tables make it easier to filter by employee or date and usually copy a formula down when you add rows.
Format the Date column as a date and the Start and End columns as times. Select the relevant cells, then use Home > Number Format to choose an appropriate format. A familiar 12-hour display such as 8:30 AM is easy to read; a 24-hour display such as 08:30 can reduce ambiguity. Either way, Excel must recognize the entries as times, not text. For the break column in this example, enter decimal hours: 0.5 means half an hour and 1 means one hour. Label the column clearly so nobody mistakes decimal hours for clock time.
For simpler entry, you can add a dropdown for a field such as department or status. Select the cells, then choose Data > Data Validation and set Allow to List. Use a short, controlled list of valid choices. This can reduce spelling variations that make filtering and summaries harder.
Calculate hours, including overnight shifts
Assuming the employee’s name is in A2, date in B2, start time in C2, end time in D2, and unpaid break in decimal hours in E2, enter =MOD(D2-C2,1)*24-E2 in F2. Copy the formula down the Hours worked column. For a shift from 8:00 AM to 4:30 PM with a 0.5-hour unpaid break, the result is 8 hours. For a shift from 10:00 PM to 6:30 AM with the same break, it also returns 8 hours because MOD handles the clock passing midnight.
The formula returns a decimal number of hours, so format the result as a number, not as a time of day. For example, 7.5 represents seven and a half hours. You can show two decimal places using Home > Number Format. If your organization records breaks in minutes instead, change the input convention consistently; do not mix entries such as 30 and 0.5 in the same column.
This calculation assumes each shift is less than 24 hours and that the entered break should be subtracted from the shift. It does not decide whether a break is paid, whether a break was actually taken, or how a particular pay rule treats the time. Those are policy and jurisdiction questions, not spreadsheet calculations.
Summarize daily and weekly hours
One row per employee per day makes errors easier to find than putting an entire week into a single cell. To see a particular employee’s total, filter the table by name and date range, then review the visible rows. For recurring reports, a PivotTable can group hours by employee and date: select the table and choose Insert > PivotTable. Place Employee in Rows, Date in Rows or Filters, and Hours worked in Values. Confirm that the Values field is summarized by Sum rather than Count.
For a small team, a weekly total can also be calculated in a separate summary area. Assuming employee IDs are in A2:A20, week-start dates are in B2:B20, and daily hours are in F2:F20, =SUMIFS($F$2:$F$20,$A$2:$A$20,H2,$B$2:$B$20,I2) adds hours matching both criteria. In this layout, H2 contains the employee ID and I2 contains the week-start date; if the matching rows total 38.5 hours, the formula returns 38.5. The date values must match the dates in column B exactly. A PivotTable is often easier to maintain when the underlying table grows.
Weekly totals are not automatically the same thing as regular and overtime pay. Overtime rules can depend on location, workweek definition, employee classification, daily limits, collective agreements, and other factors. A threshold such as 40 hours is not universal. Treat any overtime calculation in a workbook as a configured rule that must be confirmed by the employer’s payroll or HR team, rather than assuming a generic spreadsheet rule applies.
Make entries easier to check
Simple controls can prevent avoidable errors. Freeze the header row with View > Freeze Panes > Freeze Top Row so labels remain visible while scrolling. Use filters to inspect one employee or week at a time. Apply clear instructions above the table, especially for break units and time format. Protecting formula cells can reduce accidental edits, although sheet protection is not a substitute for access control or a secure storage location.
Conditional formatting can call attention to unusual entries. For example, select the Hours worked cells and choose Home > Conditional Formatting > Highlight Cells Rules to flag values above a threshold for review. A flag is only a prompt to investigate; a long shift may be valid, and a short shift may also be correct. Avoid treating a color or warning as proof of an error.
Use a consistent file-naming and approval routine, such as a separate workbook for each reporting period or a controlled master workbook stored in an approved location. Keep an untouched template so changes to a working copy do not affect future periods. Before payroll processing, compare totals with source records, ask employees or supervisors to resolve missing or inconsistent entries, and retain records according to the organization’s applicable requirements.
Common problems and their fixes
- Excel treats a time as text: The value may not calculate as expected. Re-enter it using a recognized time format, and check that the cell is not storing text imported from another system.
- A shift ending after midnight looks negative: Use the MOD-based formula above rather than simply subtracting the start time from the end time.
- A break is subtracted incorrectly: Confirm the unit. In the example formula, 0.5 means half an hour, not 30 minutes.
- A total displays as a clock time: Format calculated hours as a number. Clock-time formats can wrap after 24 hours and make totals misleading.
- Totals omit new rows: Use an Excel Table or expand the formula and PivotTable source range when adding records.
- Two people’s hours are combined: Use a stable employee ID and check that the summary includes the correct employee and date criteria.
Another common misconception is that a formula makes a time sheet payroll-compliant. A formula can perform arithmetic exactly as written, but it cannot determine whether the inputs, break treatment, workweek, rounding policy, or applicable employment rules are appropriate. Assign a person to review both the setup and the completed entries.
Excel time sheets compared with alternatives
Excel is a practical choice when a team is small, schedules are relatively stable, and someone can maintain the workbook. It is familiar, adaptable, and useful for calculations and summaries. Its weaknesses become more apparent when many employees edit the same file, when approvals must be auditable, or when managers need real-time access from multiple locations. Shared workbooks can also create version confusion if copies are emailed or saved in different folders.
A paper form may be sufficient for a very small operation, but totals must be transferred and checked manually. A dedicated time-tracking service can offer employee logins, approval workflows, reminders, and integrations, though features and costs vary by provider. A payroll platform may connect approved hours directly to payroll. Compare options based on the number of employees, approval needs, scheduling complexity, reporting, data access, and the systems already in use—not just the cost of a spreadsheet.
Frequently Asked Questions
Can Excel calculate employee hours automatically?
Yes. Enter start and end times in recognized time cells and use a formula that accounts for overnight shifts and break units. Someone still needs to check the entries and confirm the intended pay rules.
How do I calculate a shift that ends the next day?
With start time in C2, end time in D2, and an unpaid break in decimal hours in E2, use =MOD(D2-C2,1)*24-E2. The MOD function accounts for the time passing midnight, assuming the shift is shorter than 24 hours.
Should breaks be entered as minutes or decimal hours?
Either can work if the workbook is designed for that unit, but use one convention consistently and label it. In the example here, 30 minutes is entered as 0.5 decimal hours.
Can Excel calculate overtime?
It can apply a specified rule, but the correct rule depends on the employee’s circumstances and applicable requirements. Confirm the workweek and overtime policy with the responsible HR or payroll professional before relying on a calculation.
Is an Excel time sheet enough for payroll?
It may provide a useful record for a small team, but the workbook alone does not validate hours or provide a complete approval and compliance process. Review entries, retain records appropriately, and consider dedicated software if the team needs stronger controls.
Related
- Excel Data Validation Icon: What It Looks Like and Does
- Data Validation Multiple Selection in Excel: What Works
- Data Validation Interview Questions and Strong Answers
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Date Calculation Methods and FormulasIn Excel, calculate dates by adding or subtracting dates and day counts, or use functions such as DATE,…
- Excel Date Math: Formulas, Serial Numbers, and ExamplesExcel date math works by treating dates as numbers: subtract one date from another to count elapsed days,…
- Budget Spreadsheet Examples: Templates and FormulasBudget spreadsheet examples range from a simple monthly income-and-expense list to zero-based, annual, and small-business budgets. For a…
- COUNT Function in Excel: Examples and Common UsesTo count numeric values in Excel, use =COUNT(B2:B10) when the values are in B2:B10. COUNT includes numbers and…
- DATEDIF Spreadsheet Examples: Years, Months, and DaysIn Excel for Microsoft 365, use DATEDIF(start_date,end_date,unit) to calculate the elapsed time between two dates in completed years,…
- Excel Extract Text Between Quotes with FormulasIf the text is in A2, use =IFERROR(TEXTBEFORE(TEXTAFTER(A2,""""),""""),"") in Microsoft 365 or Excel 2024 to return the text…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.