A practical daily timesheet template in Excel can use one row per employee per workday, with columns for the date, employee, start time, end time, unpaid break minutes, and net hours. In Microsoft Excel for Microsoft 365 on Windows, enter =MAX(0,MOD(D2-C2,1)*24-E2/60) in F2, assuming start time is in C2, end time in D2, and unpaid break minutes in E2; it returns paid hours as a decimal and also handles a shift that passes midnight.
What a daily Excel timesheet records
A daily timesheet is a record of when work begins and ends on a particular date, together with any unpaid break time and the resulting hours. It is more useful than a handwritten total when a manager or employee needs to check how the number was calculated. A clear record also makes it easier to spot missing entries, correct an error, and summarize hours for a week or pay period.
The template should capture only the information needed for its purpose. A small team might need a date, employee name, start and end times, break duration, and total hours. A project-based business may also need a project or task code, while a supervisor may need a notes or approval field. Adding fields without a clear use can make daily entry slower and increase the chance that people leave something blank.
A timesheet is a recordkeeping tool, not a substitute for an employer’s timekeeping policy or applicable requirements. Rules for breaks, rounding, overtime, and record retention can depend on the workplace and jurisdiction. Confirm those rules with the appropriate employer contact or qualified professional before treating a spreadsheet’s calculation as an official determination.
Choose a layout that is easy to fill in
For a straightforward template, put one workday on each row and use consistent headings. A useful starting layout is:
- A: Date — the calendar date associated with the shift.
- B: Employee — a name or approved employee identifier.
- C: Start — the time the shift begins.
- D: End — the time the shift finishes.
- E: Unpaid break (minutes) — enter a number such as 30, not a time-of-day value.
- F: Net hours — a calculated decimal total.
For example, a row might show a date, Jordan Lee, 9:00 AM, 5:30 PM, 30 break minutes, and 8.00 net hours. The elapsed span is eight and a half hours; subtracting a half-hour unpaid break leaves eight paid hours. If employees work on several jobs in one day, add a project column or use a separate row for each project segment, but decide how the day’s start, end, and break information will be represented before copying the design across the team.
Build the sheet and enter the calculation
In Excel for Microsoft 365 on Windows, type the headings into row 1 and use rows below for entries. Select the range and choose Insert > Table; confirm that the table has headers. A table makes it easier to filter by employee or date, and Excel can extend formatting and calculated-column formulas when new rows are added. On Mac, the Insert tab also includes the Table command; the appearance of the ribbon may differ. Excel for the web has an Insert > Table option as well.
With the layout above, enter this in F2 and fill it down the column:
=MAX(0,MOD(D2-C2,1)*24-E2/60)
The formula treats Excel’s stored time values as fractions of a day. Multiplying the elapsed time by 24 converts it to hours, and dividing break minutes by 60 converts them to hours. MOD wraps a negative time difference across midnight: for example, a shift from 10:00 PM to 6:00 AM is eight hours elapsed before breaks. MAX prevents the result from dropping below zero if a break entry is accidentally longer than the elapsed shift. With a 9:00 AM start, 5:30 PM end, and 30 break minutes, the formula returns 8.
This example assumes the end time is on the same calendar day or on the next day if it is earlier than the start. It does not model multiple clock-in periods, paid breaks, split shifts, or unusual schedules. For those cases, enter separate work intervals or adapt the layout so every interval can be calculated and reviewed rather than forcing the data into a single start-and-end pair.
Format times, dates, and totals correctly
Excel stores dates and times as numeric values, so display formatting matters. Format the date column as a date, and the start and end columns as times using Home > Number or the Number Format menu. For a readable total, format the net-hours column as a number with two decimal places. The decimal value 8.5 means eight and a half hours; it is not the same display as 8:30, which is a duration-style hours-and-minutes display.
If you prefer to display totals as hours and minutes, use a duration format such as [h]:mm. The square brackets allow a total to display beyond 24 hours instead of wrapping like a clock. Choose one convention and label the column accordingly. Mixing decimal hours and hours:minutes can lead to misunderstandings—for example, 8.30 decimal hours is 8 hours and 18 minutes, not 8 hours and 30 minutes.
To make entry more consistent, format columns before staff begin using the sheet. You can use Data > Data Validation to restrict entries or show an input message, though time validation needs to be configured for the organization’s intended range. A simple instruction beside the break heading—“enter unpaid break in minutes”—can prevent the common mistake of typing 0:30 into a field that expects 30.
Add daily and weekly totals
If one employee’s daily entries occupy rows 2 through 6 and net hours are in F2:F6, enter =SUM(F2:F6) in the weekly total cell. For example, if those five rows contain 8, 7.5, 8, 8, and 6.5 hours, the formula returns 38. Keep the total outside the range being summed, or use a separate summary area, so the total does not include itself and create a circular reference.
For a shared sheet with multiple employees, a weekly summary can use filters or a PivotTable to group recorded hours by employee and date. A PivotTable is useful when there are many rows, but it needs refreshing after source data changes unless refresh behavior is configured. For a small sheet, filtering the table and using a clearly labeled total may be enough. Check that a summary covers the intended dates and employees; a correct formula can still produce a misleading report if its range omits recently added rows.
Overtime calculations require particular care. As a purely illustrative spreadsheet calculation, if daily net hours are in F2 and a hypothetical policy threshold is eight hours, =MAX(0,F2-8) returns the hours above that threshold. With F2 equal to 9.5, it returns 1.5. This does not establish what counts as overtime: actual rules may depend on a workweek, agreements, local requirements, or other factors. Treat such a column as a configurable estimate, not a legal conclusion.
Handle overnight shifts, breaks, and corrections
The MOD-based formula is useful when a shift crosses midnight, but the date column needs an agreed meaning. Some organizations record the date the shift started; others need a separate end date to represent longer or unusual shifts precisely. If a shift can last more than one day, or if employees can clock out and back in, a single date plus two time cells is insufficient. Add an end-date field or separate interval rows so the elapsed time is unambiguous.
Decide whether breaks are entered as minutes, as a start and end time, or through another approved method. A fixed unpaid break can be entered as a number of minutes, but that approach does not record when the break occurred. If the timing matters, use separate break-start and break-end columns and calculate break duration from those times. Avoid silently assuming that a break was taken or unpaid; the sheet should reflect actual entries and the applicable policy.
When correcting a timesheet, keep the original entry or use an approved correction process if an audit trail is required. A basic workbook may not preserve a reliable history of who changed a cell and when. Protecting formula cells can reduce accidental edits, but it does not make a workbook tamper-proof. For important records, establish who may edit, how approvals happen, and where completed copies are stored.
Make the template easier to review
Use a restrained format that helps users distinguish information they enter from values Excel calculates. For example, use one fill color for input columns and another for formula columns, freeze the header row, and turn on filters through the table design. Conditional formatting can flag blank required entries or unusually long days, but any rule should be transparent and reviewed against the organization’s policy. A visual warning is not proof that an entry is wrong.
Consider adding a status or approval field only if someone will maintain it. A short notes field can explain an adjustment, but it should not become a place for sensitive personal information. Give the workbook a clear file name and include the date range in the sheet or file name when copies are saved. Before distributing a new version, test a normal shift, an overnight shift, a blank break, and a correction so obvious formula or formatting errors are caught.
Excel template versus a dedicated timekeeping system
An Excel timesheet is flexible, familiar, and suitable for simple workflows where users can enter and review their own hours. It can be adapted without purchasing a specialized application, and the rows can be summarized with ordinary Excel tools. It is also easy to create inconsistent copies, overwrite formulas, or miss a change when several people edit separate files.
A dedicated timekeeping system may offer employee sign-in, approval workflows, change history, reminders, and integrations, depending on the product and plan. Those capabilities can matter when a business has many employees, complex shifts, or formal audit needs. They also introduce setup, subscription, and data-management considerations. The right choice depends on the scale and requirements of the process; a spreadsheet is not automatically inadequate, but it should not be relied on for controls it does not provide.
Frequently Asked Questions
How do I calculate hours worked in Excel with a lunch break?
With start time in C2, end time in D2, and unpaid break minutes in E2, use =MAX(0,MOD(D2-C2,1)*24-E2/60) in F2. For 9:00 AM to 5:30 PM with a 30-minute break, the result is 8 hours. Format the result as a number if you want decimal hours.
Can an Excel timesheet calculate an overnight shift?
Yes, the MOD formula above handles a shift that ends after midnight on the following day, such as 10:00 PM to 6:00 AM. For shifts longer than one day or multiple work intervals, add dates or separate interval rows so the spreadsheet can represent the schedule accurately.
Should timesheet totals be decimals or hours and minutes?
Either can work if the format is clear and consistent. Decimal hours are convenient for arithmetic and payroll imports; an 8.5 total means eight hours and 30 minutes. A duration format such as [h]:mm displays hours and minutes, while a normal clock format can wrap totals at 24 hours.
How do I total a week of timesheet hours?
If daily totals are in F2:F6, enter =SUM(F2:F6) in a separate weekly-total cell. Confirm that the selected rows cover the correct dates and that the total cell is not included in its own sum range.
Is an Excel timesheet enough for official records?
That depends on the employer’s process and the requirements that apply. A basic workbook can calculate and organize entries, but it may not provide a dependable change history, approval controls, or other features a particular workplace needs. Check the applicable policy and requirements before relying on it as the sole recordkeeping system.
Related
- Convert an Excel Binary Worksheet (.XLSB) to Excel
- Content Calendar Template Excel: Create and Use One
- Ctrl+G in Excel: Open Go To and Select Cells
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel INDIRECT Named Range: How It Works and When to Use ItIn Excel, INDIRECT turns text into a cell or range reference, so you can use it to refer…
- How to Copy Formulas in Excel by Dragging the Fill HandleTo copy a formula in Excel by dragging, select the cell with the formula, point to the small…
- Excel Top N Formula: Find the Highest Values and RowsTo return the top N numbers in Excel for Microsoft 365, use =LARGE(B2:B20,SEQUENCE(3)) to spill the three largest…
- Excel Time Between Two Timestamps: Formulas and TipsIf the start timestamp is in A2 and the end timestamp is in B2, enter =B2-A2 to calculate…
- Excel Average Days Between Multiple Dates: Formulas and TipsWith dates in chronological order in A2:A6, calculate each gap in a helper column and average those gaps:…
- IF Function in Excel: Examples and Practical UsesIn Excel, the IF function checks whether a condition is true and returns one result if it is,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.