Excel Time Sheet Sample: Layout, Formulas, and Tips

Formulas & functions · Updated November 18, 2026

An Excel time sheet sample is a worksheet for recording each workday’s start time, unpaid break, end time, and hours worked. The example below calculates daily hours and separates regular time from hours above an illustrative eight-hour daily threshold; copy the layout into Excel for Microsoft 365 on Windows and adjust it to your workplace’s rules.

Copyable weekly time sheet sample

Use one row per workday. The break columns below record an unpaid break as the time it begins and ends. The figures are examples, not records for a real employee.

Day Start Break begins Break ends End Total hours Regular hours Overtime hours
Monday 8:30 AM 12:00 PM 12:30 PM 5:00 PM 8.0 8.0 0.0
Tuesday 8:00 AM 12:00 PM 12:30 PM 5:00 PM 8.5 8.0 0.5
Wednesday 8:30 AM 12:30 PM 1:00 PM 5:00 PM 8.0 8.0 0.0
Thursday 8:15 AM 12:15 PM 12:45 PM 5:15 PM 8.5 8.0 0.5
Friday 8:30 AM 12:00 PM 12:30 PM 4:00 PM 7.0 7.0 0.0
Week total 40.0 39.0 1.0

For a usable sheet, put the headings in cells A1:H1 and the first workday in row 2. In this layout, A is Day, B is Start, C is Break begins, D is Break ends, E is End, F is Total hours, G is Regular hours, and H is Overtime hours. Enter real Excel time values in columns B:E, not text that merely looks like a time. Add one row for each day and copy the formulas down.

How the formulas calculate hours

Excel stores times as fractions of a day. Subtracting a start time from an end time gives a fraction, so multiplying the result by 24 converts it to decimal hours. With start, break-start, break-end, and end times in B2:E2, put this in F2: =((C2-B2)+(E2-D2))*24. For the Monday example, the two work periods are 3.5 hours and 4.5 hours, so F2 returns 8. Copy the formula down through the workweek.

This calculation assumes the break is unpaid and that the shift does not cross midnight. It adds the time before and after the break; it does not count the break as work. If breaks are paid, or the employee takes several breaks, the calculation needs to reflect that arrangement rather than silently subtracting a break that should count as work.

For the sample’s illustrative daily threshold, with the calculated decimal hours in F2, enter this in G2: =MIN(F2,8). It returns 8 for an 8-hour day and also returns 8 for an 8.5-hour day. With total hours in F2, enter this in H2: =MAX(F2-8,0). It returns 0 for an 8-hour day and 0.5 for an 8.5-hour day. Copy both formulas down. These formulas classify time using a chosen threshold; they do not determine what an employer must pay or what counts as overtime.

To total the five sample rows, with daily hours in F2:F6, put this in F7: =SUM(F2:F6). It returns 40. With regular hours in G2:G6, =SUM(G2:G6) in G7 returns 39. With overtime hours in H2:H6, =SUM(H2:H6) in H7 returns 1. The arithmetic reconciles: 39 regular hours plus 1 hour classified as overtime equals 40 total hours.

Set up and format the workbook

Formatting makes a time sheet easier to enter and review. Select the start, break, and end columns, then choose Home > Number Format and select a time format such as 1:30 PM. Select the calculated hour columns and use a number format with one or two decimal places. The sample expresses hours as decimals, so 8.5 means eight and a half hours, not eight hours and five minutes.

Build the sheet in a few deliberate steps:

  1. Enter a clear title, such as Weekly Time Sheet, and add fields for the employee, department, and week ending date if those details are needed.
  2. Use one header row and one row per shift or workday. Include only fields needed to record, calculate, review, and approve time.
  3. Enter the formulas in the first data row and copy them down for the period. Check that the final row contains a total formula rather than a copied daily calculation.
  4. Save a blank master copy. Make a fresh copy for each reporting period so earlier records are not overwritten.
  5. Test the sheet with a known example, including a day with a break and a day with no overtime, before relying on it for real entries.

You can select the range and choose Insert > Table to make a structured Excel table. Tables make it easier to filter and sort entries, and formulas in a calculated column usually fill down as rows are added. Keep the total row separate or use the table’s total-row feature carefully so it does not get included in its own sum. On Excel for Mac and Excel for the web, the ribbon names and available controls can vary slightly by version, but the basic time arithmetic and table concept are the same.

Choose a layout that matches the work

A weekly sheet with one row per day is simple for a fixed schedule and a single unpaid break. It is quick to scan, and weekly totals fit neatly below the daily entries. It is less suitable when a person works several separate shifts in one day, has multiple breaks, or needs a detailed record of each clock punch.

For variable shifts, use one row per shift instead. Useful columns might include date, shift start, break start, break end, shift end, total hours, project or job code, and reviewer. The employee can have multiple rows for the same date, and a weekly summary can then add those rows. This design keeps individual shifts visible, which helps when a schedule changes or a time entry needs correction.

Decide what the sheet is meant to document before adding columns. A time record may need a person’s name, work date, start and finish times, break information, total hours, and approval. A project time log may also need a project code or task. A payroll calculation may require additional rules and review that a basic sample does not provide. Avoid collecting unnecessary personal details, and label whether hours are elapsed, paid, billable, or assigned to a particular category.

Common time sheet errors

A result of 0.33 instead of 8 often means the formula returns a fraction of a day and is missing the multiplication by 24, or that the result is formatted as a decimal fraction. Conversely, a result that looks like a time of day instead of a number of hours may mean a duration is being displayed with a clock-time format. Check both the formula and the cell’s number format before changing the underlying data.

Other frequent problems include:

  • Times entered as text: A value may look like 8:30 AM but not behave as a time. Re-enter it in a recognized time format and check that Excel aligns and calculates it as expected.
  • Breaks counted incorrectly: Confirm whether the break is paid or unpaid and whether all breaks are represented. The sample subtracts one unpaid break.
  • Formula not copied to every row: Compare formulas in several rows, especially after inserting a new row. A blank formula cell can make totals appear too low.
  • Totals displayed incorrectly: If using a duration rather than decimal hours, a standard clock display can wrap after 24 hours. Use a duration format such as [h]:mm for cumulative durations.
  • Unexplained differences: Check the original time entries, rounding settings, missing shifts, and whether the weekly total covers the intended dates.

Do not overwrite a questionable entry just to make the total match an expected number. Keep the original record, identify the correction, and use whatever review or approval process applies to the organization. A spreadsheet can calculate from its inputs but cannot confirm that a punch or break entry is true.

Overnight shifts and policy limits

The simple F2 formula assumes that each shift segment begins and ends on the same calendar day. For a shift that crosses midnight, ordinary subtraction can produce a negative duration. With start in B2, break start in C2, break end in D2, and end in E2, use this alternative in F2: =(MOD(C2-B2,1)+MOD(E2-D2,1))*24. For a shift from 10:00 PM to 2:00 AM, followed by a 30-minute break and work until 6:00 AM, it returns 7.5 hours: two hours before the break plus five and a half after it. This assumes the break is unpaid and that each listed interval is less than 24 hours.

The eight-hour split in the sample is only an example of how to classify hours. Overtime thresholds, workweek definitions, rounding practices, required records, and treatment of breaks can depend on local rules, contracts, and workplace policy. A weekly overtime rule also differs from a daily threshold: the sample’s daily formulas cannot be treated as a universal payroll calculation. Confirm the relevant requirements and have a qualified payroll or compliance professional review a sheet used for official records.

Rounding deserves particular care. A formula that rounds each day can produce a different weekly result from one that totals exact entries first and rounds afterward. Do not add rounding merely to make numbers look cleaner. If a policy requires rounding, document the rule, apply it consistently, and make clear whether displayed totals are rounded or exact.

When Excel is enough and when to choose another tool

Excel is useful when a small team needs a flexible log, totals, filters, and a familiar workbook that can be adapted without setting up a dedicated system. A workbook can also be printed or saved as a PDF for review. Its limitations matter, though: formulas can be overwritten, multiple copies can drift apart, and a shared file does not automatically provide a dependable clock-in process or an audit trail.

For a larger team, rotating schedules, multiple locations, approval chains, mobile punch-in, or payroll integration, dedicated time-tracking software may reduce manual handling. Before choosing one, compare its export options, access controls, approval history, support for the organization’s rules, and total cost. A more specialized tool still needs correct configuration and oversight; it does not make inaccurate source entries accurate.

If you share an Excel workbook, limit access to people who need it and avoid including sensitive information that is not required for timekeeping. Protecting cells that contain formulas can reduce accidental edits, but worksheet protection is not a substitute for appropriate file access controls or secure storage. Retain records according to the applicable workplace process and requirements.

Frequently Asked Questions

How do I calculate hours worked in Excel with a lunch break?

Subtract the start-to-break interval and the break-to-end interval, then multiply the combined time by 24 to show decimal hours. The sample formula assumes one unpaid break and a shift that does not cross midnight.

How do I make 8.5 hours display as hours and minutes?

Decimal 8.5 is eight and a half hours, which is 8 hours and 30 minutes. If you want a duration display instead, keep the result as an Excel time fraction and apply a duration format such as [h]:mm; do not interpret the decimal digits as minutes.

Can I use this sample for overnight shifts?

Use the MOD-based formula in the overnight section to handle a time interval crossing midnight. The basic subtraction formula can return a negative value when the end time is earlier on the clock than the start time.

Does the sample calculate legally required overtime?

No. It demonstrates an illustrative daily eight-hour threshold only. Actual overtime treatment can depend on applicable rules, the defined workweek, and workplace arrangements, so confirm the appropriate method before using a sheet for payroll.

How do I add more workdays to the sample?

Add another row, copy the daily formulas into it, and expand the ranges in the weekly total formulas to include the new row. If the range is an Excel table, a new table row can help extend calculated-column formulas automatically, but verify the totals afterward.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

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

Spotted an error in this guide? Let us know.