Timesheet Template in Excel: Formulas for Hours, Breaks and Overtime

Short answer: a timesheet template in Excel has one row per day with time in, time out and unpaid break, and calculates hours worked with =MOD(D5-C5,1)-E5, which also handles shifts that cross midnight. Format totals as [h]:mm so weekly hours above 24 display correctly, convert to decimal hours with *24 for payroll, and split each workweek into regular and overtime hours with MIN and MAX.

This guide builds a weekly and a biweekly timesheet step by step, shows the overtime and pay formulas with a worked example, and adapts the same sheet for freelancers who bill clients by the hour. It covers U.S. federal basics; state rules and your own policies may add requirements, so check them before relying on the sheet for payroll.

Timesheet layout

Above the table, add employee name, employee ID, department, the week-starting date and the hourly rate. Below it, the totals and lines for employee and supervisor signatures with dates.

Step 1: Dates that fill themselves

Enter the first day of the workweek in B2 (for example, Sunday or Monday, matching your payroll workweek). Then A5 is =B2 and A6 is =A5+1, copied down for seven days. Changing B2 rolls the whole sheet to a new week. A conditional formatting rule such as =WEEKDAY($A5,2)>5 shades weekends.

Step 2: Hours worked, including overnight shifts

Excel stores times as fractions of a day, so subtracting them gives the elapsed time:

• Hours worked (F5): =IF(D5="","",MOD(D5-C5,1)-E5)

The MOD(…,1) part is what makes a 10:00 PM to 6:30 AM shift come out as 8:30 instead of an error. Use it on every row so nobody has to remember which formula applies to night shifts.

If employees take a split shift or several breaks, add a second pair of in/out columns rather than squeezing everything into one break cell: =MOD(D5-C5,1)+MOD(G5-F5,1) for two work periods.

Step 3: Weekly totals that display correctly

Sum the week with =SUM(F5:F11) and format that cell as [h]:mm. Without the square brackets, 44 hours shows as 20:00 because Excel rolls over every 24 hours. For payroll, use the decimal column: =SUM(G5:G11) gives 44.5 instead of 44:30.

Step 4: Regular hours, overtime and pay

Under the federal Fair Labor Standards Act, non-exempt employees must receive at least one and a half times their regular rate for hours worked over 40 in a workweek. Some states add daily overtime or other rules, so treat the formulas below as the federal baseline:

• Regular hours: =MIN(40,TotalDecimalHours)
• Overtime hours: =MAX(0,TotalDecimalHours-40)
• Regular pay: =RegularHours*Rate
• Overtime pay: =OvertimeHours*Rate*1.5
• Gross pay: =RegularPay+OvertimePay

Worked example

At an illustrative rate of $22.00 per hour: regular pay is 40 × $22.00 = $880.00, overtime is 4.5 × $33.00 = $148.50, and gross pay is $1,028.50. The figures are an example; your payroll provider or accountant handles withholding and deductions.

Biweekly timesheets: keep the workweeks separate

Many businesses pay every two weeks, but federal overtime is calculated per workweek, not per pay period. A week of 45 hours followed by a week of 35 hours includes 5 overtime hours, even though the two-week total is exactly 80. Build the biweekly sheet as two weekly blocks, each with its own regular and overtime lines, and add them together only at the pay line.

Rounding time

If you round punches, do it consistently and neutrally — rounding that always favors the employer is a common compliance problem. To round to the nearest quarter hour, use =MROUND(C5,"0:15") on the in and out times. Many employers simply record actual minutes, which avoids the question entirely.

PTO, holidays and sick time

Add a column for paid hours that are not worked — vacation, holiday, sick leave — and keep it separate from hours worked. Under federal rules, overtime is based on hours actually worked, so paid time off does not usually count toward the 40-hour threshold, but company policy, state law or a union contract may treat it differently. Separate columns let you apply whichever rule applies without rebuilding the sheet.

Timesheet for freelancers and client billing

The same structure works for billable hours. Replace the overtime block with three columns — Client, Project and Task — and add an hourly rate per client in a lookup table. Then:

• Billable amount per row: =G5*XLOOKUP(I5,Rates!A:A,Rates!B:B)
• Hours per client this month: =SUMIFS(G:G,I:I,"Client A",A:A,">="&StartDate,A:A,"<"&EDATE(StartDate,1))

A short task description on every row makes invoices easier to explain and disputes rarer.

Protect the template

Lock every formula cell and leave only the in, out, break and notes cells unlocked, then turn on sheet protection so totals and pay cannot be overwritten. Our guide on how to protect formulas in Excel walks through the steps.

Record-keeping basics

The FLSA requires employers to keep accurate records of hours worked and wages paid for non-exempt employees. Federal rules call for keeping payroll records for at least three years and the time cards on which wages are computed for at least two years; state rules can be longer. Have employees and supervisors sign or approve each timesheet, and store the approved versions as PDFs that cannot be edited later.

Common mistakes

• Typing times as text (8.30 instead of 8:30). Excel cannot calculate with them.
• Totals without [h]:mm, which show the wrong number of hours once a week passes 24.
• Averaging overtime across two weeks. Calculate it per workweek.
• Counting unpaid meal breaks as work time, or the reverse for short rest breaks; follow federal and state rules on which breaks are paid.
• No approval step, leaving no record of what was agreed if hours are disputed.

Frequently asked questions

How do I calculate hours between two times in Excel?

Subtract the start time from the end time and multiply by 24 for decimal hours: =(D5-C5)*24. Use MOD(D5-C5,1) instead of D5-C5 if the shift may cross midnight.

Why does my total show 20:00 instead of 44:00?

The cell is formatted as a time of day. Apply the custom format [h]:mm so Excel shows total elapsed hours.

Can I use this timesheet in Google Sheets?

Yes. MOD, MIN, MAX, SUMIFS and the duration format work the same way. Google Sheets is convenient when employees enter their own hours from a phone.

Is a spreadsheet timesheet enough for compliance?

Federal law does not prescribe a specific format, but records must be accurate and complete. A spreadsheet can work for a small team; as headcount grows, a time clock or time-tracking app with audit trails reduces errors and disputes.

More templates for running a small business are collected in Operations & HR, including our meeting minutes template.