A timesheet spreadsheet template is a worksheet for recording work dates, start and end times, unpaid breaks, and total hours. In Excel for Microsoft 365 on Windows, create columns for those entries, use a formula to calculate each shift, and add a weekly total so hours can be reviewed before approval.
What a timesheet template should record
A useful timesheet makes it possible to see who worked, when they worked, and how the total was calculated. For a simple weekly sheet, use one row per employee per workday, or one row per shift if people can work multiple shifts in a day. Include these fields:
- Employee name or ID: Use a consistent identifier, especially if more than one person has the same name.
- Work date: Enter the date the shift began. This matters when a shift crosses midnight.
- Start time and end time: Record the actual times worked, rather than relying on a planned schedule.
- Unpaid break minutes: Enter the total break time to subtract from the shift. If breaks are paid, do not subtract them in this field.
- Hours worked: Calculate this field with a formula rather than retyping a total.
- Project, department, or job code: Include this when hours must be assigned to different work or cost categories.
- Notes and approval: Use notes for corrections or unusual shifts and record who reviewed the submitted time.
For a very small team, a separate tab for each week may be easy to read. A single log with one row per shift is often more flexible for filtering and summarizing several weeks. Choose one approach and use it consistently; mixing layouts makes totals and reviews harder.
Set up the worksheet in Excel
In a blank workbook, put the headings in row 1, for example: Employee, Date, Start, End, Break (minutes), Hours, Project, Notes, and Approved. Select the heading row and a few blank rows beneath it, then press Ctrl+T on Windows and confirm that the table has headers. Excel tables make it easier to filter entries, keep a consistent style, and extend formulas when new rows are added. On a Mac, use Command+T to create a table; the dialog and available options can vary slightly by Excel release.
Format the Date column as a date, and Start and End as times. In Excel for Windows, select the cells and use Home > Number Format, or open Format Cells with Ctrl+1. Apply a number format such as 0.00 to Hours so a result like 7.5 is shown as decimal hours. Avoid typing a leading apostrophe before a time or number: that can store an entry as text and prevent calculations. Data validation through Data > Data Validation can restrict entries, but it is not a substitute for checking the timesheet.
Keep the raw entries separate from any summary. For example, the shift log can hold daily records while a summary area or separate worksheet shows a person’s weekly total. If the sheet is shared, explain which cells employees should edit and which cells contain formulas. A clear legend or lightly shaded input cells can help prevent accidental changes.
Calculate hours, including overnight shifts
Excel stores times as fractions of a day. Subtracting a start time from an end time therefore gives a fraction, not a decimal number of hours. The MOD function handles a shift that ends after midnight, provided the shift is shorter than 24 hours. With Start in B2, End in C2, and unpaid break minutes in D2, enter this formula in E2, the Hours column:
=MOD(C2-B2,1)*24-D2/60
For example, a shift from 9:00 AM to 5:30 PM lasts 8.5 hours. If D2 contains 30 minutes, the formula returns 8.00 hours: 8.5 minus 30 divided by 60. For an overnight shift from 10:00 PM to 6:00 AM with a 30-minute unpaid break, it returns 7.50 hours. Copy the formula down the column, or enter it in an Excel table column and allow the table to fill the formula into other rows.
This simple calculation assumes both entries are valid times and that the elapsed shift is under 24 hours. It will not identify a missed clock-out, an incorrectly entered break, or an unusual shift lasting more than a day. Add a review step for incomplete or implausible entries. If shifts may run for 24 hours or longer, use actual date-and-time values for both the start and end rather than relying on time-only entries.
Weekly totals and overtime tracking
A weekly total is useful for reviewing submitted hours, but overtime rules depend on the applicable workplace policy and jurisdiction. Do not assume that a particular threshold or calculation applies everywhere. A spreadsheet can apply a chosen threshold for a planning or review purpose, but it does not determine which rules govern a person’s pay.
For one employee whose seven daily hour totals are in E2:E8, put the weekly total in a separate summary cell and enter:
=SUM(E2:E8)
If the daily values are 8, 8, 8, 8, 8, 3.5, and 0, the result is 43.5 hours. If a clearly illustrative review uses a 40-hour threshold, place that weekly total in F2 and put this formula in G2 for hours above that threshold:
=MAX(0,F2-40)
With F2 equal to 43.5, the formula returns 3.5; with F2 equal to 38, it returns 0. This is only a mathematical example, not a statement that 40 hours is the correct overtime threshold for every worker. If the threshold changes, replace 40 with a cell reference containing the chosen value, and have the responsible payroll or HR professional confirm the relevant policy. Do not treat the result as a complete payroll calculation: pay rates, differentials, rounding rules, and other factors may require separate handling.
For multiple employees, summarize by employee and week rather than adding every row into one company-wide total. A pivot table is one option: select the log, choose Insert > PivotTable, then arrange employee and date fields as appropriate and summarize Hours by sum. Make sure the source data includes a real date column and numeric hours. A pivot table is a summary aid; it does not correct inaccurate source records.
Make entries easier to review
Small formatting choices can prevent common mistakes. Freeze the header row with View > Freeze Panes > Freeze Top Row so column labels remain visible while scrolling. Turn on filters through the table headings to find one employee, date range, or project. Use consistent time formats and avoid merged cells in the log because they interfere with sorting and filtering.
Conditional formatting can highlight blank or questionable records. For example, select the Hours column and use Home > Conditional Formatting > Highlight Cells Rules to flag unusually high values. A highlighted value is a prompt to investigate, not proof that the entry is wrong. A legitimate long shift may exceed the threshold, while a plausible-looking number can still be inaccurate.
Before collecting entries, define how to handle edits: employees should correct their own records or submit a documented correction, and reviewers should be able to tell whether a row changed after approval. Keep a protected original or dated export if a history is needed. Worksheet protection can reduce accidental formula edits, but it is not strong security for sensitive information; share files only with people who need access and follow the organization’s record-handling practices.
Common spreadsheet problems
Hours show as a time of day. Excel may display a duration like 0.333 as 8:00 AM if the cell has a clock-time format. Format decimal-hour results as Number, such as 0.00. If using a duration-style calculation instead, use a duration format such as [h]:mm so totals over 24 hours display correctly.
A negative result appears for an overnight shift. Ordinary subtraction of 10:00 PM from 6:00 AM is negative. The MOD formula above accounts for a midnight crossing for a shift under 24 hours. Check that the entries are actual Excel times and not text.
The total is wrong by the break amount. Confirm whether the break is paid and whether the input is in minutes. The example formula expects minutes: enter 30, not 0.5, for a 30-minute break. Entering 0.5 in a minutes field subtracts only half a minute.
Sorting separates a value from its employee. Sort the whole Excel table, not an isolated column. Selecting just one column can detach hours from the employee and date on the same row. Tables help keep records together when sorting by a heading.
Totals omit recent rows. Check that the new entries are inside the table or inside the ranges used by summary formulas. If a formula uses a fixed range such as E2:E8, it will not include row 9. Expand the range or use a table-based summary method. Always compare a sample of calculated rows with the original time entries.
Spreadsheet template or time-tracking app?
A spreadsheet is a practical choice when a team has a modest number of records, needs a custom layout, and can rely on someone to review entries and maintain the file. It is also easy to adapt for project codes or an approval column. Microsoft 365 licensing and organizational access determine which Excel features and sharing options are available, so check the plan and workplace settings rather than assuming every user has the same setup.
A dedicated time-tracking or payroll system may be more suitable when many workers submit time, managers need approval workflows, records must connect to scheduling or payroll, or audit trails and access controls are important. Such products differ in their features and cost. Compare the actual workflow, export options, permissions, and fees before choosing one. A spreadsheet can still serve as an export or review tool, but avoid maintaining two competing records without a clear source of truth.
Frequently Asked Questions
Can I use a timesheet spreadsheet template for multiple employees?
Yes. Keep one row per employee per shift, include a consistent employee ID or name, and filter or summarize by employee and week. Avoid separate, inconsistently structured logs if you need reliable combined reporting.
How do I calculate hours when a shift crosses midnight?
With start time in B2, end time in C2, and break minutes in D2, use =MOD(C2-B2,1)*24-D2/60. This handles overnight shifts shorter than 24 hours, assuming the entries are valid Excel times.
Should breaks be deducted from a timesheet?
Only deduct breaks that the timesheet’s rules treat as unpaid. Record break duration consistently and confirm the applicable workplace policy; do not assume every break should be subtracted.
Can Excel calculate overtime automatically?
Excel can calculate hours above a threshold you specify, using a formula such as =MAX(0,F2-40) when F2 contains weekly hours. The threshold and any other overtime rules must be confirmed for the relevant worker and situation.
Is a spreadsheet timesheet enough for payroll?
It can provide hour records, but the sheet alone does not establish that entries are correct or calculate every pay requirement. Have an authorized reviewer check records and use the organization’s payroll process.
Build a template that fits the workflow
Start with only the fields needed to record and approve work: employee, date, start, end, break, calculated hours, and any required project code. Test the formula with an ordinary shift and an overnight shift, verify the weekly total, and explain the input conventions before sharing the file. A simple, consistently maintained timesheet is more useful than a complicated workbook whose assumptions and formulas are unclear.
Related
- How to Create a Timeline in PowerPoint: Step-by-Step
- Debt Snowball Spreadsheet UK: Build One in Excel
- Savings Tracker Spreadsheet Template for Excel
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel File Merge Online Free: Safe Ways to Combine WorkbooksYes, you can merge Excel files online for free, but the best method depends on what you mean…
- How to Add an Attachment to a Word Mail MergeTo add a different attachment to each Word mail-merge email, put each recipient’s file path in your data…
- How to Create a Book Template in Microsoft WordCreate a reusable book template in Microsoft Word by setting the page size and margins, applying paragraph styles,…
- How to Archive Emails in Outlook to the CloudTo archive Outlook email in the cloud, move messages into the Archive folder of a cloud-hosted account, or—if…
- Clean Excel Workbook: A Practical Guide to Cleaner DataTo clean an Excel workbook, first save a copy, then check its structure, standardize inconsistent values, verify data…
- Active Print Area in Excel: Meaning and SetupIn Excel, the “active print area” usually means the cell range currently designated to print on a worksheet;…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.