How to Make a Timesheet in Excel

Create a weekly or bi-weekly timesheet in Excel with time formats, the hours formula, break deduction, overtime split and a print-ready layout.

Updated 2026-10-20

This builds the same timesheet our Excel template contains, so you understand every cell.

Layout

Formats

C7:D13 as Time (1:30 PM). F7:F13 as Number, 2 decimals. B4 and F17:F19 as Currency.

Formulas

F7, filled down:

=IF(OR(C7="",D7=""),0,(D7-C7+(D7<C7))*24-E7/60)

The $ signs let you copy the block to a second week without the references drifting.

Bi-weekly

Duplicate the sheet (right-click the tab > Move or Copy > Create a copy) for Week 2 and add a Summary sheet: ='Week 1'!F19+'Week 2'!F19. Keep overtime per week; a two-week total of 80 hours can still contain 10 overtime hours.

Daily overtime

Insert Regular and Daily OT columns: =MIN(F7,8) and =MAX(0,F7-8). Sum the Regular column and test it against 40 for weekly overtime. The overtime template does this.

Printing

Page Layout > Fit to 1 page wide by 1 tall. Add signature lines in A21:A22. Set the print area to A1:F22. Save as a template (.xltx) so each week starts clean.

Data validation

Select C7:D13, Data > Data Validation > Time between 0:00 and 23:59, so typed numbers like 900 are rejected with a message.

When to stop using Excel

Multiple employees, multiple rates, state overtime presets, rounding rules and share links are where a spreadsheet gets fragile. The Timesheet Calculator handles those and exports to .xlsx when you need the file.

Adding a lunch column

Insert two columns after Time in: Lunch out and Lunch in. The hours formula in the last column becomes:

=IF(OR(C7="",F7=""),0,((F7-C7+(F7<C7))-(E7-D7+(E7<D7)))*24)

Delete the Break (min) column or keep it for a second short unpaid break; subtract it as -G7/60 at the end of the formula.

Multiple employees in one workbook

One tab per employee named after them, each a copy of the weekly sheet, and a Team tab that lists names in column A with =INDIRECT("'"&A2&"'!F19") in column B to pull each gross pay by tab name. INDIRECT does not update when a tab is renamed, so keep names stable. For more than a dozen people a single table with an Employee column and a pivot table is easier to maintain.

Military time entry

If your clock prints 24-hour times such as 1730, format C7:D13 with the custom format hh:mm and type the colon (17:30). Excel will not understand a bare 1730; if you must paste four-digit values, add a helper column with =TIME(INT(C7/100),MOD(C7,100),0) and point the hours formula at it.

Frequently asked

Why does my overtime show as 0 even though I worked 45 hours? The total is probably being compared to a cell that is formatted as time (40:00 is 1.667 as a number). Make sure D4 contains the number 40, not a time.

Why do I get #VALUE!? A time cell contains text, usually because it was typed with a trailing space or with letters such as “9am” in a version of Excel that does not parse that form. Retype as 9:00 AM.

Can Excel round to the nearest quarter hour for me? Yes, =MROUND(C7,"0:15") rounds a time cell to 15 minutes. Apply it to the punch cells, not the daily hours, to follow the legal method.

How do I total more than 24 hours? Use decimal hours as in this guide, or if you prefer the time format, apply [h]:mm so the display does not wrap at 24.

Is this template compliant for payroll records? It records in, out, breaks and totals, which is what the FLSA requires employers to keep for two years (three for payroll records). It does not replace a time clock’s audit trail; edits in a spreadsheet leave no history unless you keep each week’s file unchanged after approval.