To make a simple timesheet in Excel, enter Date, Start, End, Break (minutes), and Hours in columns A:E, then use =MOD(C2-B2,1)*24-D2/60 in E2 and fill it down. This calculates elapsed hours, subtracts an unpaid break, and also handles a shift that ends after midnight; add a weekly total with =SUM(E2:E8).
What a simple Excel timesheet should track
A useful basic timesheet records when work happened and how many hours count toward the period being summarized. One row per shift or workday is a practical starting point. Put the column headings in row 1: Date, Start, End, Break (minutes), and Hours. Enter the date, start time, end time, and break for each shift in rows 2 onward. The Hours column is calculated rather than typed by hand.
For example, a row might contain a date, 9:00 AM, 5:30 PM, and a 30-minute break. The formula returns 8 hours. Keeping the break as a number of minutes makes the input straightforward: enter 30 for half an hour, not 0:30. Decide what the break column means before using the sheet. If it records only unpaid breaks, paid rest periods should not be deducted there.
This compact layout is suitable for a personal record, a small team’s draft timesheet, or a simple weekly summary. It is not automatically a payroll system. An employer may need additional fields, approvals, project codes, or rules for paid time, leave, overtime, and rounding.
Build the template in Excel
- Open a blank workbook in Excel for Microsoft 365 on Windows. Type the five headings in cells A1:E1.
- Enter one shift per row, keeping each value in its own column. Use actual Excel date and time values rather than typing a combined sentence such as “Monday, 9 to 5.”
- Put the hours formula in E2, then copy or fill it down for the remaining rows. If you convert the range into a table with Insert > Table, Excel can extend the calculated column as you add rows.
- Format column A as a date, columns B and C as times, and column E as a number with two decimal places. Format the break column as a whole number.
- At the end of a weekly block, label the summary row and add a sum of the daily hours. Save a clean copy as a reusable template before entering actual records.
Excel stores dates and times as numbers, with time represented as a fraction of a day. Applying a time format changes how the value appears, not the underlying value. That is why the formula can subtract a start time from an end time and multiply by 24 to express the result in hours.
Calculate daily hours and breaks
With dates in A2:A8, start times in B2:B8, end times in C2:C8, break minutes in D2:D8, and calculated hours in E2:E8, enter this formula in E2 and fill it down:
=MOD(C2-B2,1)*24-D2/60
For a shift from 9:00 AM to 5:30 PM with 30 minutes entered in D2, the formula returns 8. The time difference is 8.5 hours, and the break deduction is 0.5 hours. Format E2:E8 as Number with two decimal places to display 8.00. The displayed decimals are for readability; they do not change the underlying calculation.
The MOD function makes an end time earlier than the start time count as a shift crossing midnight. For example, 10:00 PM to 6:00 AM with a 30-minute break returns 7.5 hours. This assumes the end time is on the next calendar day whenever it is earlier than the start time. For an unusual schedule where that assumption is wrong, record the actual dates and use a date-and-time design instead. If a shift lasts longer than 24 hours, this simple time-only formula is not appropriate.
Check that breaks are entered consistently and that start and end cells contain real times. A value entered as text may not calculate as expected. If the template will be shared, a short note near the headings can define whether breaks are entered in minutes and which types of break are deducted.
Add weekly totals and optional overtime tracking
With daily calculated hours in E2:E8 and the weekly total intended for E9, enter this in E9:
=SUM(E2:E8)
If five workdays each contain 8 hours and the other two rows are blank, the total is 40. Blank cells are ignored by SUM. Label D9 as Weekly total so the summary is clear, and use a number format consistent with the daily results.
An optional overtime estimate can be useful for checking a timesheet, but a spreadsheet threshold is only a rule selected for the sheet. It does not establish which hours qualify under an employer policy or applicable requirements. With the weekly total in E9 and a hypothetical 40-hour threshold, put this in F9:
=MAX(0,E9-40)
At 40 total hours the result is 0; at 42 it is 2. If the relevant threshold or period differs, change the setup to match the rule supplied by the employer or payroll administrator. Do not assume a single weekly threshold covers every work arrangement.
Format the sheet so it is easy to review
Formatting helps people spot missing or inconsistent entries, but it cannot validate whether a reported shift is accurate. Use a bold, contrasting header row and freeze it through View > Freeze Panes > Freeze Top Row so headings remain visible while scrolling. Use a sensible date format, such as a month-day-year format that is unambiguous to the people sharing the workbook.
To make the entry area easier to use, select the data range and choose Insert > Table. Tables provide filter controls and can extend formatting and formulas when rows are added. Alternatively, use Home > Format as Table. Avoid merged cells in the data area: they make sorting, filtering, and copying rows more difficult. Keep explanatory notes outside the rows of shift data.
You can also use Data > Data Validation to limit entries in a column, such as requiring a whole number for break minutes. Validation can prevent some accidental inputs, but it is not a substitute for reviewing the records. If several people will use the workbook, add an example row or a brief instruction that makes the expected time and break formats explicit.
Common timesheet problems and checks
- Hours show as a time of day: The result column may be formatted as Time. Change it to Number, or use the duration option below.
- The result is negative or unexpectedly large: Confirm that start and end values are real times and that the overnight assumption fits the shift. Check whether a break was entered in minutes rather than hours.
- A total looks wrong: Confirm the sum range includes every daily result and does not include a summary cell that would count hours twice. Check for text values or missing formula cells.
- Numbers appear rounded: Two decimal places affect display only. If an organization requires a particular rounding method, follow its stated policy rather than adding an arbitrary rounding rule.
- Rows added later are missing calculations: Fill the formula into the new rows or use an Excel table so the calculated column can extend with the data.
One alternative is to display a duration such as 8:00 instead of decimal hours. With the same input layout, put this formula in E2:
=MOD(C2-B2,1)-D2/1440
For the 9:00 AM to 5:30 PM example with a 30-minute break, this returns a duration of 8 hours. Format the result cells with the custom format [h]:mm so totals can show more than 24 hours. This formula stores time as a fraction of a day, unlike the decimal-hours formula, so do not mix the two result types in one total. Choose one representation for the whole sheet.
When a simple template is not enough
A basic workbook is easy to adapt, but it may not provide an audit trail or controlled approval process. If multiple employees submit records, a manager may need to review and approve entries, and payroll may require a specific export format. If people work on several projects, add a project or job-code column rather than putting multiple activities in one cell. For leave, on-call time, travel, or split shifts, ask which categories the organization needs before expanding the workbook.
Excel is also not inherently a secure time-clock system. A workbook can be edited, copied, or overwritten, and formulas can be changed accidentally. Keep an untouched template, limit access to shared files as appropriate, and retain the original records according to the organization’s process. For an official payroll record, use the format and procedure specified by the employer or payroll provider rather than treating this simple sheet as authoritative by itself.
Excel template versus other timesheet options
Excel makes sense when the goal is a customizable record with transparent calculations and a small number of users. It works offline in the desktop app, and a user can adjust columns or summaries without needing a dedicated time-tracking service. For collaboration, a workbook stored in OneDrive or SharePoint can be shared, subject to the organization’s access and version-control practices.
A dedicated time-tracking or scheduling tool may be a better fit when records need to be submitted from phones, linked to projects, approved in sequence, or exported directly to payroll. Those tools may involve a subscription or account setup, and their features and costs vary. A paper log can be sufficient for a personal backup, but totaling and correcting entries is more manual. Choose based on the process, not just the appearance of the timesheet.
Frequently Asked Questions
Does Excel have a simple timesheet template?
Excel may offer templates through its template or new-workbook experience, but availability can vary. You can also create the five-column layout above in a blank workbook and save it as a reusable template.
How do I calculate hours worked in Excel with a lunch break?
Enter the start and end times in separate cells, enter the unpaid break in minutes, and subtract it with =MOD(C2-B2,1)*24-D2/60. This assumes the inputs follow the column layout described above and that an earlier end time means the shift ended the next day.
How do I total hours worked for a week?
If daily decimal hours are in E2:E8, use =SUM(E2:E8) in the weekly summary cell. Make sure the range covers the intended days and excludes any other summary total.
Can the Excel timesheet calculate an overnight shift?
Yes. The MOD-based daily formula treats an end time earlier than the start time as a next-day end. It is suited to ordinary overnight shifts, not shifts longer than 24 hours or schedules where the dates need to be recorded explicitly.
Should timesheet totals be entered as decimal hours or hours and minutes?
Either can work if the whole sheet uses one consistent method. Decimal hours such as 7.5 are convenient for arithmetic; a duration such as 7:30 may be easier to read. Format duration totals as [h]:mm so totals over 24 hours display correctly.
Can I use this timesheet as an official payroll record?
That depends on the employer’s process and recordkeeping requirements. The template is a calculation aid, not a guarantee that its fields, approvals, or rules meet a particular organization’s needs. Confirm the required format with the employer or payroll administrator.
Related
- Excel Print: Keep Row and Column Headers on Every Page
- Excel Print Header on Each Page: Repeat Rows in Print
- Excel Print Top Row on Every Page
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Formula for Quarterly Dates: Examples and TipsTo calculate the first day of the calendar quarter containing a date in A2, use =DATE(YEAR(A2),3*INT((MONTH(A2)-1)/3)+1,1). For example,…
- Excel IF Formula Examples: Tests, Logic, and ErrorsExcel’s IF formula checks whether a condition is true or false and returns one result for each outcome.…
- Custom Text Filter in Excel: How to Use ItTo apply a custom text filter in Excel for Microsoft 365 on Windows, select a cell in your…
- Calculate Percentage in Excel: Formulas and Table TipsTo calculate a percentage in Excel, divide the part by the total, then format the result as a…
- Filter Excel Table Without a Header: What WorksExcel filtering needs a header row: if you apply Filter to a headerless range, Excel treats its first…
- Copy and Transpose in Excel: Steps, Formulas, and TipsTo copy and transpose data in Excel for Microsoft 365 on Windows, select and copy the cells, choose…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.