Excel Work Timesheet Template: Build One That Adds Work Hours

How-to basics · Updated November 19, 2026

An Excel work timesheet template can be made with columns for the date, start time, end time, unpaid break, and hours worked. In Microsoft Excel for Microsoft 365 on Windows, enter the shift details in each row and use =IF(OR(B2="",C2=""),"",MAX(0,(MOD(C2-B2,1)-D2)*24)) to calculate decimal hours, including shifts that cross midnight.

What an Excel work timesheet records

A work timesheet is a record of when someone worked and how much time they worked during a chosen period. A simple spreadsheet can help an employee keep a personal record, let a manager review submitted hours, or provide a consistent format for a small team. It is not automatically a payroll system: it will only calculate the values its formulas and entries support, and it does not decide which hours count as paid time under a workplace’s rules.

Before building the sheet, decide what it needs to document. A basic timesheet may be enough for a person with one work period per day. A team that tracks different projects, locations, or multiple shifts in a day may need extra fields or a different layout. Agree on how breaks, leave, corrections, and approvals should be recorded before relying on the workbook.

Set up the columns and weekly rows

Start a blank workbook and put a clear title and the relevant employee name and date range above the table. Use one row per workday, with these headings in row 1:

  • A: Date
  • B: Start
  • C: End
  • D: Unpaid break
  • E: Hours worked

Rows 2 through 8 can represent seven consecutive days. If the employee works only five days, the remaining rows can stay blank. Put a weekly total beneath the entries, such as in E9. Add columns for project, task, notes, or approval only when they serve a clear purpose; unnecessary fields make the form slower to fill out and harder to review.

Enter start and end values as times, such as 8:00 AM and 4:30 PM. Enter a 30-minute unpaid break in D as a time duration, such as 0:30. That is different from entering the number 0.5: Excel stores times as fractions of a day, so 0.5 means twelve hours unless the value is converted or displayed differently.

Calculate hours, including an overnight shift

With the layout above, put the hours formula in E2. It leaves the result blank until both shift times are present, subtracts the break, and uses MOD to handle a shift whose end time falls after midnight. Copy the formula down the workday rows using the fill handle at the lower-right corner of E2.

With start time in B2, end time in C2, and unpaid break duration in D2, enter =IF(OR(B2="",C2=""),"",MAX(0,(MOD(C2-B2,1)-D2)*24)) in E2. For a shift from 8:00 AM to 4:30 PM with a 0:30 break, the result is 8.00 hours: 8.5 elapsed hours minus 0.5 break hours. For a 10:00 PM to 6:00 AM shift with a 0:30 break, the result is 7.50 hours.

The formula returns decimal hours, which are convenient for adding and comparing totals. Format the hours column as a number with two decimal places, rather than as a clock time. The MAX function prevents a negative result if a break is longer than the calculated shift, but a zero result should be treated as a warning to check the entry—not proof that the record is correct. This formula is designed for shifts shorter than 24 hours; a shift lasting 24 hours or more needs date-and-time entries that capture the full start and end dates.

Add weekly totals and optional overtime columns

With daily decimal hours in E2:E8, enter =SUM(E2:E8) in E9 to total the week. If the entered daily results are 8, 8, 8, 8, and 10.5 hours, the weekly total is 42.50 hours. The remaining two blank rows do not change the result. A total formula is useful for checking the week, but it cannot tell whether the source entries or break treatment are correct.

If a workplace wants a simple comparison against a set weekly threshold, add columns for regular hours and hours above that threshold. These are only arithmetic examples: overtime eligibility, thresholds, daily limits, and treatment of different kinds of time depend on applicable rules and workplace policy. Do not assume that a spreadsheet’s threshold determines what an employee is owed.

With the weekly total in E9 and an illustrative threshold of 40 hours, enter =MIN(E9,40) in F9 for hours at or below 40, and =MAX(0,E9-40) in G9 for hours above 40. For the 42.50-hour example, F9 returns 40.00 and G9 returns 2.50. Change the threshold only when the appropriate rule or approved policy calls for it, and confirm actual requirements with a qualified professional when needed.

Format the sheet so time is easy to read

Formatting distinguishes clock times from calculated hours and reduces avoidable entry mistakes. Select the start and end columns, then use Home > Number Format to choose a time display. Select the break column and choose a duration format that can show elapsed hours and minutes. For totals recorded as time rather than decimal hours, the custom format [h]:mm is useful because elapsed totals can exceed 24 hours; an ordinary clock display can wrap around after a day.

For the decimal-hours formula in column E, use Home > Number Format and choose Number, then set two decimal places. You can also open Format Cells with Ctrl+1 on Windows or Command+1 on Mac and set the number format there. Keep the input columns visually distinct from formula columns, for example with a light fill for editable cells and a different fill for calculated cells. A note near the headings can state whether breaks should be entered as hours and minutes and whether blank rows should be left empty.

Freeze the heading row if the timesheet is long, and make sure dates include the year when records might be reviewed later. A printed form should fit the relevant columns on the page without shrinking the text until it is difficult to read. Before sharing or printing, check that employee names and any confidential notes are included only when needed.

Choose a template or make your own

A prebuilt Excel timesheet can save setup time, while a custom sheet gives more control over fields and calculations. Excel’s available templates can vary by installation and account. In Excel, use File > New and search for a timesheet template; inspect the workbook before entering real records, since templates differ in layout, formulas, and assumptions.

Check a downloaded template rather than trusting its appearance. Confirm that the date range and employee fields suit the use case, test the break and overnight-shift calculations with known examples, and inspect whether totals include all intended rows. Remove irrelevant fields, but do not delete formula cells casually: other totals may depend on them. Keep an untouched copy before adapting a template so you can compare results or restore the original structure.

  • Choose a basic daily sheet for one start and end time per day.
  • Choose a project-based sheet when hours must be allocated among clients or tasks.
  • Choose a multi-shift layout when a person can have more than one work period on a date.
  • Use a separate approval field or signature process if review is part of the organization’s procedure.

Common errors and ways to prevent them

The most frequent problem is a value entered as text instead of a time. If a formula gives an unexpected result, select the cell and check that Excel recognizes the entry as a time; re-enter it using a clear time format if necessary. Another common issue is entering a break as 0.5 while the formula expects 0:30. That mismatch can produce a dramatically incorrect result because Excel interprets numeric values and time fractions differently.

Other checks are worth building into the review routine:

  • Confirm the formula was copied into every day row and that the weekly total covers the full intended range.
  • Check that a missing end time has not been mistaken for a completed shift.
  • Review overnight shifts and unusually long or short totals against the original record.
  • Use consistent rules for rounding and corrections; do not silently change a recorded time just to make a total look tidy.
  • Test formulas with sample data before using the workbook for real records, then compare a few results by hand.

For recurring use, consider protecting formula cells from accidental edits while leaving the input cells available. Protection is not a substitute for controlling access to a sensitive workbook. Store timesheets where only appropriate people can view or change them, and use a clear process for naming, dating, reviewing, and retaining files. Payroll and attendance records can contain personal information, so avoid sharing a workbook broadly just because it is convenient.

When a spreadsheet is not the right tool

An Excel workbook works well when the process is straightforward and someone can review entries and formulas. It becomes less convenient when many employees submit hours, managers need approval histories, shifts span locations or projects, or the organization needs automatic reminders and controlled audit trails. In those cases, a dedicated time-tracking or workforce-management system may reduce manual handling, although its features and costs vary.

A spreadsheet also requires care when multiple people edit it. Cloud storage and shared workbooks can support collaboration, but access settings and editing practices still matter, and a formula can be overwritten. A dedicated system may be more suitable when a business needs centralized permissions, change history, integrations, or reporting. Compare the actual requirements and costs rather than assuming that either a spreadsheet or an app is automatically more accurate.

Frequently Asked Questions

How do I calculate work hours in Excel?
Enter the start time, end time, and unpaid break consistently, then use a formula that subtracts the break from the elapsed shift. For a shift under 24 hours, the MOD-based formula above also handles an end time after midnight. Format the result as decimal hours if you want to sum hours directly.

How do I calculate a shift that ends after midnight?
Use a formula that wraps the difference between end and start times with MOD, such as the formula in this guide. A shift from 10:00 PM to 6:00 AM is eight elapsed hours before breaks. For shifts of 24 hours or longer, record full dates and times instead of relying on time-only entries.

Should a timesheet show hours as decimals or hours and minutes?
Either can work if the format is clear and consistent. Decimal hours make arithmetic convenient: 7.5 means seven and a half hours. An elapsed-time display such as 7:30 is familiar to many readers, but totals should use a format such as [h]:mm so they do not wrap at 24 hours.

Can I use an Excel timesheet to calculate overtime?
Excel can compare a total with a threshold, but that calculation is not a decision about legal eligibility or pay. Rules may depend on location, work arrangement, and other details. Use only a threshold confirmed for the relevant situation and seek qualified guidance when requirements are uncertain.

Where can I find an Excel timesheet template?
In Excel for Microsoft 365, open File > New and search for timesheet. You can also create a simple sheet with date, start, end, break, and hours columns. Whichever route you choose, test its formulas and confirm its assumptions before entering actual work records.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.