Free Excel Timesheet Template (With Formulas)

An Excel timesheet template turns a grid of clock times into automatic totals — if you set the formulas up correctly. Excel is powerful for this, but it also has a famous quirk: it stores time as a fraction of a day, which trips up anyone who expects "8:30" to mean 8.5 hours. This guide shows how to build a working template with formulas, handle breaks and overtime, and sidestep the formatting traps.

Columns to set up

Create headers across row one:

• Date
• Time In
• Time Out
• Break (mins)
• Hours Worked (formula)
• Regular and Overtime (formulas)

Format the Time In and Time Out columns as time, and the Break column as a plain number of minutes.

The core formula

To get hours worked from a start and end time, subtract them and multiply by 24, then subtract the break:

=(C2-B2)*24-(D2/60)

Here C2 is Time Out, B2 is Time In, and D2 is break minutes. The *24 is the key move — Excel stores 6:00 PM as 0.75 (three-quarters of a day), so multiplying by 24 converts the fraction back into hours. Skip it and your totals will be nonsensically small.

For shifts that cross midnight, use =(C2-B2+(C2<B2))*24-(D2/60) so a clock-out "earlier" than clock-in still calculates correctly.

Totaling the week

Sum the Hours Worked column with a simple =SUM(E2:E8). Because you already converted to decimal hours in each row, the weekly total behaves normally — no further tricks needed.

Splitting regular and overtime

With a weekly total in cell E9, split at 40 hours:

• Regular: =MIN(E9,40)
• Overtime: =MAX(E9-40,0)

That caps regular hours at 40 and pushes anything above into overtime. Adjust the 40 if a different threshold applies to you.

Formatting tips that prevent errors

• Format result cells as Number, not Time. If Hours Worked is formatted as time, "7.5 hours" displays as "7:30 AM" and confuses everyone.
• Enter times with AM/PM or use 24-hour entry consistently.
• Lock the formula cells so people fill in times without overwriting the math.
• Show two decimals so 7.5 and 7.75 are clearly distinct.

Where Excel templates fall short

Formulas total hours, but the spreadsheet can't do the rest:

• People still type times from memory, so accuracy depends on honesty and recall.
• Version control breaks down when several people email copies back and forth.
• No approvals or audit trail, so a changed cell leaves no history.
• Overtime rules beyond a simple weekly cap get complicated fast.

Once those become the bottleneck, a dedicated tool is worth the switch. Timesheet Maker captures real clock-ins from any device, totals and splits overtime automatically, and keeps an approvals-ready record — no formulas to maintain and no copies to reconcile.

Frequently asked questions

How do I calculate hours worked in Excel? Subtract Time In from Time Out and multiply by 24 to convert Excel's day-fraction into hours, then subtract any break: =(Out-In)*24-(BreakMins/60).

Why does my Excel timesheet show the wrong total? Almost always the missing *24, or a result cell formatted as Time instead of Number. Excel stores time as a fraction of a day, so both are needed.

How do I handle overnight shifts? Add a correction so a clock-out before the clock-in still works: =(Out-In+(Out<In))*24.

Is a spreadsheet good enough for payroll? For a couple of people, yes. As the team grows, manual entry and version chaos make errors likely — that's the point to move to time tracking software.

Build it, then outgrow it

Set the formulas up once and Excel will total your hours. When manual entry and version control start costing you, try Timesheet Maker free.