A useful Excel timesheet template keeps the original clock entries separate from calculated hours. The layout below uses full start and end dates, so an overnight shift does not need a special midnight workaround. It produces decimal hours for a weekly summary and flags missing or impossible entries before they reach the total.
Set up the worksheet
Use one sheet named Timesheet, one employee per sheet, and these headings in row 1. Enter actual Excel date/time values rather than text. The example dates use year-month-day order; if Excel does not recognize that format in your locale, enter dates using its date picker or a recognized local format.
| Column | Heading | Entry or formula |
|---|---|---|
| A | Start | Full date and time |
| B | End | Full date and time |
| C | Unpaid break minutes | Number; enter 0 when none |
| D | Check | Validation formula |
| E | Decimal hours | Duration formula |
| F | Week starting | Monday date |
Format A:B as date plus 24-hour time, C as a number, E as a number with two displayed decimals, and F as a date. Leave underlying values unrounded. Only deduct breaks that should be excluded from the work record; this sheet does not determine whether a break is legally unpaid.
Add the formulas
Put this check in D2 and copy down through row 200. Empty rows remain empty; partial entries are identified instead of silently treated as zero hours.
=IF(COUNTA(A2:C2)=0,"",IF(COUNT(A2:C2)<3,"CHECK",IF(OR(B2<A2,C2<0,C2>(B2-A2)*1440),"CHECK","OK")))Put the decimal-hours formula in E2. Excel stores dates and times in days, so multiplying the elapsed time by 24 changes the unit to hours. Divide break minutes by 60 before subtracting.
=IF(D2="OK",(B2-A2)*24-C2/60,"")Put this formula in F2 and copy both formulas down. It groups entries by the Monday containing the start timestamp.
=IF(D2="OK",INT(A2)-WEEKDAY(INT(A2),2)+1,"")This Monday grouping is a reporting choice. If a shift crosses your employer's workweek boundary, split the entry at that boundary and allocate its break to the correct segment. A start-date grouping alone cannot allocate hours correctly across two workweeks.
Build a weekly total
Enter the Monday date to summarize in H2. In I2, add the matching hours. In J2, count entries needing review; a nonzero result means the totals may omit incomplete records.
I2: =SUMIF(F2:F200,H2,E2:E200)
J2: =COUNTIF(D2:D200,"CHECK")For a weekly report with several weeks, fill down H with successive Mondays and keep the data ranges fixed using dollar signs, such as $F$2:$F$200. Extend every range together when you add more rows. This is a worksheet layout you can build, not a downloadable template.
Check the results before using it
| Start | End | Break | Result |
|---|---|---|---|
| 2026-10-19 09:00 | 2026-10-19 17:30 | 30 | 8.00 hours |
| 2026-10-20 22:00 | 2026-10-21 06:00 | 30 | 7.50 hours |
| 2026-10-21 08:00 | 2026-10-21 12:00 | 0 | 4.00 hours |
| 2026-10-22 09:00 | 2026-10-22 10:00 | 90 | CHECK; excluded |
The three valid entries total 19.50 hours for the week starting October 19. Five eight-hour entries total 40.00, not 16.00: decimal-hour totals do not wrap at 24 hours. Full timestamps also support spans over 24 hours, but ordinary spreadsheet timestamps do not automatically account for daylight-saving clock changes or different time zones.
Keep the sheet reviewable
Protect formula columns after testing, retain the original entries, and add a notes column for corrections. Do not put a weekly total into the source rows or it will be counted again. Overtime calculations require the correct workweek and applicable rule; a weekly hours total by itself is not a payroll compliance calculation.
Formula references: Microsoft time subtraction and SUMIF documentation.
Related calculators and guides
- Work Hours Calculator
- Excel: Show Total Hours Above 24
- Timesheet Control Totals: Reconcile Before Import
Explore more in Hours & Pay Calculators and Guides.