How does the timesheet calculate overtime?

It converts the week's total to decimal hours, caps Regular hours at 40 with =MIN(B15,40), and treats anything above 40 as overtime via =MAX(0,B15-40). Overtime pay then applies a 1.5× multiplier.

Why does the Hours cell show 0 or a wrong number?

Hours stays 0 until both Start and End are filled. If it looks off, check that times are real time values (like 9:00, not text). For an overnight shift where End is earlier than Start, wrap the end time with +1, as in =(E6+1)-B6, so the math crosses midnight.

How do I use the biweekly timesheet?

Open the Biweekly tab, enter your rate in B3, and log all fourteen days. Overtime is calculated separately for each week, then combined, so weeks over 40 hours are paid correctly even within one period.

Can I log a shift without a lunch break?

Yes. Leave both Lunch Out and Lunch In blank and the formula skips the deduction entirely, counting the full Start-to-End span as worked hours for that day.

How do I change the overtime rate or threshold?

Edit B19: replace the 1.5 multiplier for a different overtime premium. To change the 40-hour line, adjust the 40 in the =MIN and =MAX formulas in B16 and B17.

Timesheet Template with Overtime Formulas

Last updated June 2026

Enter start, lunch, and end times next to your hourly rate, and the math is done for you: daily hours per row, regular and overtime split at the 40-hour line, and total pay at the bottom. Two tabs cover a seven-day Weekly view and a fourteen-day Biweekly pay period — a free .xlsx for hourly workers and small employers.

timesheet.xlsx · free · no signup

Download free template

Works in Excel, LibreOffice Calc, and Google Sheets (File → Import).

SHA-256: dbed749d9166ae27a0e96a8a265e23f75d43d5b25f95ef05df8b40fbfefc92db

F6=IF(OR(B6="",E6=""),0,(E6-B6)-IF(OR(C6="",D6=""),0,D6-C6))

	A	B	C	D	E	F
1	Date	Start	Lunch Out	Lunch In	End	Hours
2	Mon 7/6	9:00	12:00	12:30	17:30	8:00
3	Tue 7/7	9:00	12:00	12:30	17:30	8:00
4	Wed 7/8	9:00	12:00	12:30	17:30	8:00
5	Thu 7/9	9:00	12:00	12:30	17:30	8:00
6	Fri 7/10	9:00	12:00	12:30	17:30	8:00

Day rows from the Weekly sheet. Hours is End minus Start, less the lunch break — a 9:00–17:30 day with a 30-minute lunch lands at 8:00.

What's inside

Sheets

WeeklyOne seven-day week (7/6–7/12/2026 prefilled). Enter times, see daily and week totals, and a pay summary that splits regular from overtime.
BiweeklyA fourteen-day pay period with the same columns. Overtime is figured per week — each week's hours over 40 count — then both weeks are summed for pay.

Columns

DateOne row per day. The sample week is already dated, so just type your times.
StartClock-in time, entered as 9:00 or 9:00 AM. Leave blank for days off.
Lunch OutWhen your unpaid break starts. Leave Lunch Out and Lunch In blank if you didn't break.
Lunch InWhen you return from break. The gap between Out and In is subtracted from the day.
EndClock-out time. Hours stays at 0 until both Start and End are filled in.
HoursCalculated daily hours: End minus Start, minus the lunch break. Formatted with the custom number format [h]:mm (set via Ctrl+1 → Number → Custom) so totals over 24 hours still display correctly instead of rolling over.

Formulas that do the work

=IF(OR(B6="",E6=""),0,(E6-B6)-IF(OR(C6="",D6=""),0,D6-C6))

Each day's Hours cell. It returns 0 until both Start and End exist, then subtracts the lunch gap only if both lunch cells are filled — so a no-lunch day still totals correctly. Copied down every day row.

=SUM(F6:F12)

The Weekly Total in F13 adds all seven day rows. On the Biweekly sheet the equivalent total in F20 runs =SUM(F6:F19) across all fourteen days.

=MAX(0,B15-40)

Overtime hours in B17. B15 is the week's decimal hours (=F13*24); anything above 40 is overtime, and =MIN(B15,40) in B16 caps regular hours at 40.

=B17*B3*1.5

Overtime pay in B19: overtime hours times the rate in B3 times 1.5. Regular pay in B18 is =B16*B3, and B20 adds the two for total pay.

How to use it

Set your hourly rate
On the Weekly sheet, put your pay rate in B3 (next to Hourly rate). The sample is 22.00 — overwrite it. Every pay figure recalculates from this one cell.
Enter your times
For each day, type Start, Lunch Out, Lunch In, and End as clock times like 9:00 or 1:30 PM. The Hours column fills in automatically; skip lunch cells on days you worked through.
Read the pay summary
Below the table, the summary shows Regular hours (capped at 40), Overtime hours, and the pay for each at 1.5× overtime. Total pay in B20 is the bottom line.
Switch to biweekly when needed
For a two-week pay period, use the Biweekly tab instead. It has fourteen day rows and figures overtime per week, so a heavy week and a light week don't cancel out.
Reuse it each period
Right-click the sheet tab, choose Move or Copy, tick Create a copy, and rename it for the new week. Clear the time cells and you're ready to log again.

Compatibility

Microsoft Excel. Excel for Microsoft 365, Excel 2016 and later (Windows and Mac).
LibreOffice Calc. Opens directly — formulas, validation lists, and formatting carry over.
Google Sheets. Upload via File → Import → Upload, or drag the file into Drive and open with Sheets.

Time math relies on Excel storing times as fractions of a day — 9:00 is 0.375 — which Excel 2016, 2019, 2021, and Microsoft 365 all share with Google Sheets and LibreOffice Calc. The [h]:mm totals and the MIN/MAX overtime split carry over without edits.

Frequently asked questions

How does the timesheet calculate overtime?: It converts the week's total to decimal hours, caps Regular hours at 40 with =MIN(B15,40), and treats anything above 40 as overtime via =MAX(0,B15-40). Overtime pay then applies a 1.5× multiplier.
Why does the Hours cell show 0 or a wrong number?: Hours stays 0 until both Start and End are filled. If it looks off, check that times are real time values (like 9:00, not text). For an overnight shift where End is earlier than Start, wrap the end time with +1, as in =(E6+1)-B6, so the math crosses midnight.
How do I use the biweekly timesheet?: Open the Biweekly tab, enter your rate in B3, and log all fourteen days. Overtime is calculated separately for each week, then combined, so weeks over 40 hours are paid correctly even within one period.
Can I log a shift without a lunch break?: Yes. Leave both Lunch Out and Lunch In blank and the formula skips the deduction entirely, counting the full Start-to-End span as worked hours for that day.
How do I change the overtime rate or threshold?: Edit B19: replace the 1.5 multiplier for a different overtime premium. To change the 40-hour line, adjust the 40 in the =MIN and =MAX formulas in B16 and B17.

Related guides

More business templates →