An Excel timesheet format is a structured worksheet for recording each employee’s work date, start and end times, unpaid breaks, and hours worked. For a simple weekly sheet, use one row per workday, calculate daily hours with a formula, and total the week with SUM; format the total as decimal hours or elapsed time, depending on how it will be reviewed.
What an Excel timesheet usually includes
A useful timesheet does more than hold a list of clock-in and clock-out times. It makes the work period clear, separates entered information from calculated results, and provides enough detail for someone else to check the record. A basic sheet may have these columns:
- Employee and period: employee name, department or project if needed, and the week beginning and ending dates.
- Work date and day: the date of each shift, with the weekday shown for quick scanning.
- Start and end: the time the shift began and ended.
- Break: unpaid break duration, entered consistently as a time value such as 0:30 for 30 minutes.
- Hours worked: a calculated result rather than a manually typed number.
- Notes or approval: a place to explain an unusual shift or record a review, if the organization needs it.
For a small team, one employee per worksheet or workbook can keep the record easy to follow. For a manager comparing several employees, a table with one row per person per date is usually easier to filter and summarize. The right layout depends on whether the sheet is for an individual record, a team log, or a payroll handoff.
Choose a layout that fits the work
A weekly grid is familiar: Monday through Sunday appear as rows, and each row holds that day’s times. It works well when one person records one shift a day and the sheet is reviewed weekly. Add a second row or a separate shift table if split shifts are common. Trying to squeeze two shifts into one start-and-end pair can make the calculation and audit trail confusing.
For irregular schedules or many employees, use a row-based log instead. Put one shift on each row and include columns for employee, date, start, end, break, and net hours. This format grows naturally as records are added and can be filtered by name or date. In Microsoft 365 on Windows, selecting the range and choosing Insert > Table creates a table with filter controls; on Mac, the Insert tab also contains the Table command. A table can make it easier to extend formatting and formulas to new rows.
Keep identifying information above a weekly grid, but do not rely on a typed label such as Week 1 to identify records. Include actual dates. Dates make it possible to distinguish one week from another and help prevent a copied sheet from being mistaken for a current record.
Set up the worksheet and enter times consistently
Start with clear headers, for example Date, Start, End, Break, and Hours Worked. Enter actual Excel dates and times, not text that merely looks like a date or time. A time such as 8:30 AM is stored by Excel as part of a day; a break of 30 minutes can be entered as 0:30. Use the same convention in every row, and make the time period visible in the headers or instructions.
Format the date column as a date and the start, end, and break columns as time or elapsed time. To apply a number format in Excel for Microsoft 365 on Windows, select the cells and press Ctrl+1, then choose a suitable category and format in the Format Cells dialog. On Mac, use Command+1. A custom format of h:mm AM/PM can make clock times unambiguous; a duration format of [h]:mm is useful for totals that might exceed 24 hours.
Use a distinct fill or font style for input cells and calculated cells, and add a short instruction such as Enter breaks as hours:minutes. Avoid relying on color alone to explain what a cell means. If people will print the sheet, check the print preview and make sure employee, date range, and column headings remain visible and legible on the page.
Calculate daily hours, including breaks
Suppose the weekly grid has the start time in C2, end time in D2, break duration in E2, and calculated hours in F2. With C2 set to 9:00 AM, D2 set to 5:30 PM, and E2 set to 0:30, enter this formula in F2:
=(MOD(D2-C2,1)-E2)*24
The result is 8 hours: the shift spans 8.5 hours, less a 30-minute break. The MOD function allows an end time after midnight to be treated as the next part of the shift. For example, a shift beginning at 10:00 PM and ending at 6:00 AM spans eight hours before subtracting any break. Format F2 as a number, such as 2 decimal places, because this formula returns decimal hours; a value of 7.5 means seven and a half hours.
Copy the formula down for the other workdays, checking that the cell references move to each row. If a shift can last more than 24 hours, or if date and time need to be recorded precisely across midnight, enter full date-and-time values in the start and end cells instead. With the complete start date and time in C2, complete end date and time in D2, and break duration in E2, calculate decimal hours with:
=((D2-C2)-E2)*24
This version uses the actual date difference, so the end date must be correct. Do not use the first formula to represent a full 24-hour shift with identical start and end times: MOD treats that difference as zero. Record the end date as well when that situation is possible.
Total the week and choose a display
If daily decimal hours are in F2:F8, enter this in the weekly total cell:
=SUM(F2:F8)
For the example of five identical 8-hour shifts, the result is 40. Format a decimal-hours total as a number, not as a time of day. Excel may otherwise display a total in a way that looks like a clock reading rather than a duration.
Another valid approach is to calculate daily durations without multiplying by 24. With start in C2, end in D2, and break duration in E2, use:
=MOD(D2-C2,1)-E2
Format the daily result and the weekly SUM cell as [h]:mm. The same five 8-hour shifts then display as 40:00. The brackets matter: a format such as h:mm can roll over after 24 hours and show a misleading smaller number. Decimal hours are often easier to compare or transfer into systems that expect values such as 8.5; elapsed hours and minutes are often easier for people to read. Do not mix the two approaches in one total.
Make the timesheet easier to review
A timesheet should make missing or unusual entries noticeable without silently changing the record. Consider a separate notes column for missed punches, split shifts, or corrections, and make it clear who entered or reviewed a change when that is part of the process. Use consistent date ranges and retain the original entries when corrections need an audit trail.
For a larger log, an Excel table can help people sort and filter by employee or date. Freeze the header row with View > Freeze Panes > Freeze Top Row in Excel for Windows; the View tab on Mac also provides Freeze Panes options. Conditional formatting can draw attention to blanks or values that need review, but it should not be treated as proof that a timesheet is correct. A manager still needs to check the underlying dates, times, and break entries.
Before sharing, confirm the worksheet does not expose other employees’ records unnecessarily, and decide whether people should edit the formulas. Protecting a worksheet can reduce accidental changes, but it is not a substitute for appropriate file permissions or a secure recordkeeping process. Keep an untouched copy or controlled source if later review matters.
Common timesheet errors and how to avoid them
- Entering hours as clock times: typing 8.5 in a time-formatted cell does not mean eight and a half hours. Enter clock values in time cells and use a number format for decimal-hour results.
- Forgetting to subtract a break: label the break column and state whether its entries are durations. A 30-minute break should be 0:30, not 0.30, which is not 30 minutes in Excel time.
- Using the wrong total format: a total over 24 hours can appear to wrap around if displayed as h:mm. Use [h]:mm for elapsed time or a numeric format for decimal hours.
- Misreading an overnight shift: a time-only formula can handle a typical crossing of midnight, but date-and-time entries are clearer for long shifts or records that span dates.
- Overwriting formulas: distinguish input cells from calculated cells and check totals after copying rows. A formula replaced by a typed value will not update when the source times change.
One common misconception is that Excel automatically knows whether a time is paid or unpaid. It does not. The sheet only calculates from the entries and rules built into it. Break treatment, rounding, overtime, approvals, and record-retention requirements can depend on an employer’s procedures and applicable rules, so a generic workbook should not be treated as a definitive payroll policy.
Templates, alternatives, and when Excel is enough
Excel includes templates that can be a convenient starting point. In Excel for Microsoft 365 on Windows, open File > New and search for timesheet; available templates can vary by account, region, and product updates. Review the formulas and labels before using a template, since its assumptions about breaks, pay periods, or overtime may not match the way a particular team records work.
A custom workbook is useful when a person needs a straightforward weekly record, a small group shares a consistent process, or the organization wants to inspect and adapt its calculations. A dedicated time-tracking or payroll system may be more suitable when many people need to submit records, managers need approval workflows, shifts are complex, or records must connect to other business systems. A spreadsheet can be shared and edited, but version conflicts and accidental formula changes become harder to manage as the number of users grows.
Whatever tool is chosen, establish who enters and reviews the hours, how corrections are documented, and where the completed records are stored. Test a copy with ordinary and overnight shifts before using it for live records. A readable format and clear process are at least as important as a clever formula.
Frequently Asked Questions
How do I make a timesheet in Excel?
Make columns for date, start, end, break, and hours worked. Enter times consistently, calculate each day with a formula, and sum the daily results. Label the employee and date range so the record can be identified later.
How do I calculate hours worked in Excel after a break?
With start in C2, end in D2, and break duration in E2, use =(MOD(D2-C2,1)-E2)*24 for decimal hours. With a 9:00 AM start, 5:30 PM end, and 0:30 break, the result is 8.
How do I calculate an overnight shift?
For a typical shift that crosses midnight, a time-only formula using MOD can calculate the elapsed duration. If the shift may exceed 24 hours or you need an unambiguous record, enter both start and end as full date-and-time values and subtract them.
Should timesheet totals be decimal hours or hours and minutes?
Use decimal hours when the result needs to be entered into a system expecting values such as 7.5. Use [h]:mm when people need to read a duration as hours and minutes. Choose one convention for the total and format the cells to match it.
Can Excel calculate overtime automatically?
Excel can calculate hours using rules that someone specifies, but a generic template cannot determine which overtime rules apply. Confirm the applicable policy and requirements before building an overtime calculation into a timesheet.
Related
- Excel Data Validation Guidelines for Reliable Spreadsheets
- Excel Data Validation From Another Workbook: Best Methods
- Data Validation From a Table in Excel: Setup and Fixes
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Attendance Format: A Clear Monthly Attendance TrackerAn Excel attendance format is a spreadsheet that lists each person in a row and each date in…
- Split Text and Numbers in Excel: A Step-by-Step GuideTo split letters and digits from the same Excel cell, put the mixed value in A2, then use…
- Excel Tables: Do They Automatically Add New Rows in Excel?Yes. In Microsoft Excel for Microsoft 365 on Windows, a table will usually expand automatically when you type…
- Google Sheets ARRAYFORMULA: How It Works and ExamplesIn Google Sheets, ARRAYFORMULA lets one formula return results for multiple rows or columns, rather than calculating for…
- How to Add a Cover Page in Microsoft Word Step by StepIn Word for Microsoft 365 on Windows, select Insert > Cover Page, choose a design, and replace its…
- How to Create a Custom Signature in Microsoft OutlookCreate a custom Outlook signature by opening the signature settings for your version of Outlook, adding your sign-off…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.