TEMPLATES & BUSINESS WORKFLOWS

Payroll and Hours Worked in Excel: Timesheet Formula with Breaks

Working out hours from a paper timesheet is slow and easy to get wrong, especially with lunch breaks, a shift that runs past midnight or a week that goes into overtime. This tutorial builds a weekly Excel timesheet that subtracts unpaid breaks, handles overnight shifts, totals the week correctly and turns the hours into regular pay, overtime pay and gross pay.

Excel timesheet with breaks: the hours worked formula =MOD(Out-In,1)-MOD(BreakEnd-BreakStart,1) gives 45:30 for the week, 45.50 hours, 5.50 overtime hours and gross pay of $1,061.50. — The finished timesheet: hours per day, the weekly total, overtime and gross pay.

What you will build

Maria Lopez is an hourly, non-exempt employee at Brightline Trading Co. paid $22.00 an hour. Her workweek runs Monday to Sunday, and the week of Oct 5, 2026 includes a 30-minute lunch most days, a half day with no break on Friday, and a Saturday night inventory shift from 9:00 PM to 3:00 AM.

Area	Cells	What it holds
Timesheet	A3:H11	Date, Day, Time in, Break start, Break end and Time out (inputs), then Worked and Hours
Pay inputs	J3:K5	Hourly rate, overtime threshold and overtime multiplier (inputs)
Weekly pay	J6:K13	Total, regular and overtime hours, pay, and a check

Free download: Get the timesheet with breaks workbook (.xlsx) and follow along. Yellow cells are inputs, and every formula below is already in place.

How Excel stores time

Excel stores a time as a fraction of a day. 6:00 AM is 0.25, 12:00 PM is 0.5 and 6:00 PM is 0.75. That has two consequences for a timesheet:

Subtracting two times gives a fraction of a day too. 5:00 PM minus 8:00 AM is 0.375, which Excel displays as 9:00 if the cell has a time format.
To get hours you can multiply by a pay rate, multiply by 24. 0.375 × 24 = 9 hours.

Step 1: Enter the times

In A3:H3 type the headers Date, Day, Time in, Break start, Break end, Time out, Worked and Hours.
In A4 enter 10/5/2026, in A5 =A4+1, and copy down to A10. In B4, =TEXT(A4,"ddd") shows the day name.
Type the times into C4:F10 with AM or PM, such as 8:00 AM and 12:30 PM. Format them with Format Cells > Time, or the custom format h:mm AM/PM.
Leave the break cells empty on a day without a meal break, and the whole row empty on a day off.

Excel weekly timesheet for Oct 5 to Oct 11, 2026 with Time in, Break start, Break end and Time out entered as times such as 8:00 AM and 12:30 PM. — Times are typed as real times, one row per day.

Checkpoint: Click C4 and look at the formula bar. It should show 8:00:00 AM. If the cell is left-aligned and the formula bar shows exactly what you typed, it is text and the formulas will fail; retype it with a space before AM or PM.

Which breaks to enter: Only enter unpaid meal breaks. Under the federal Fair Labor Standards Act (FLSA), short rest breaks of about 5 to 20 minutes are paid work time and must not be deducted. A meal period, usually 30 minutes or more, can be unpaid only if the employee is completely relieved from duty. Some states have their own meal and rest break rules, so check yours.

Step 2: Subtract the break from the shift

Time worked is the shift length minus the break. In G4:

=IF(OR(C4="",F4=""),"",MOD(F4-C4,1)-MOD(E4-D4,1))

F4-C4 is the shift length: 5:00 PM minus 8:00 AM = 9:00.
E4-D4 is the break: 12:30 PM minus 12:00 PM = 0:30. On a day with no break both cells are empty, so this is 0.
The IF leaves the cell blank on a day off instead of showing 0:00.
MOD is explained in the next step; on a normal day shift it changes nothing.

Format G4 as [h]:mm and copy it down to G10 (Ctrl+D fills it down in one keystroke). If the IF part is new to you, building an IF formula walks through it.

Excel hours worked formula =IF(OR(C4="",F4=""),"",MOD(F4-C4,1)-MOD(E4-D4,1)) returning 8:30 for Monday, 8:00 AM to 5:00 PM with a 30-minute lunch. — Shift length in blue, the break in orange.

Checkpoint: Monday shows 8:30: 9 hours on site minus a 30-minute lunch. Tuesday also shows 8:30 (9:15 minus a 45-minute lunch), and Friday, with no break, shows 5:00.

Step 3: Handle shifts that cross midnight

Saturday's shift starts at 9:00 PM and ends at 3:00 AM. Without MOD, 3:00 AM minus 9:00 PM is a negative number, and Excel shows ##### instead of a time. MOD(F9-C9,1) adds one day to a negative result: −0.75 becomes 0.25, which is 6:00. The same MOD around the break handles a break that crosses midnight.

Excel overnight shift from 9:00 PM to 3:00 AM with a break from 12:00 AM to 12:30 AM, where MOD returns 5:30 hours worked. — MOD turns an overnight shift into a positive number of hours.

Checkpoint: Saturday shows 5:30: 6 hours from 9:00 PM to 3:00 AM, minus the 30-minute break from 12:00 AM to 12:30 AM. MOD only works for shifts shorter than 24 hours, which covers any normal timesheet.

Step 4: Convert to decimal hours

Payroll needs hours as a number, like 8.50, not a time like 8:30. In H4:

=IF(G4="","",ROUND(G4*24,2))

Multiplying by 24 turns the fraction of a day into hours, and ROUND removes tiny floating-point remainders so the hours multiply cleanly by the pay rate. Format H4 as a number with 2 decimals and copy it down to H10.

Excel formula =IF(G4="","",ROUND(G4*24,2)) converting 8:30 to 8.50 decimal hours, 8:45 to 8.75 and 9:15 to 9.25. — The time in column G becomes decimal hours in column H.

Worked	Hours	Why
8:30	8.50	30 minutes is half an hour
8:45	8.75	45 minutes is three quarters of an hour
9:15	9.25	15 minutes is a quarter of an hour
5:30	5.50	Saturday's overnight shift

8:30 is not 8.30 hours. Typing 8.30 into a pay calculation underpays 12 minutes.

Step 5: Total the week

In G11 enter =SUM(G4:G10) and in H11 =SUM(H4:H10). The blank Sunday is ignored because the formula returned empty text.

The format matters for G11. A plain h:mm format starts again at 0 after every 24 hours, so a 45.5-hour week displays as 21:30. The custom format [h]:mm, with the h in square brackets, keeps counting past 24.

Excel weekly total formatted [h]:mm showing 45:30 and 45.50 hours, while the same total formatted h:mm wrongly shows 21:30. — [h]:mm shows 45:30; the same value formatted h:mm shows 21:30.

Checkpoint: G11 shows 45:30 and H11 shows 45.50. K12 holds the same total with the wrong h:mm format so you can see the problem: 21:30.

Step 6: Overtime and gross pay

The FLSA requires overtime of at least 1.5 times the regular rate for hours worked over 40 in a workweek by non-exempt employees. Overtime is worked out per workweek, not per day and not per pay period. Put the inputs in K3:K5 (rate $22.00, threshold 40, multiplier 1.5), then:

Cell	Formula	Result
K6 Total hours	`=H11`	45.50
K7 Regular hours	`=MIN(K6,K4)`	40.00
K8 Overtime hours	`=MAX(0,K6-K4)`	5.50
K9 Regular pay	`=ROUND(K7*K3,2)`	$880.00
K10 Overtime pay	`=ROUND(K8K3K5,2)`	$181.50
K11 Gross pay	`=K9+K10`	$1,061.50

MIN caps regular hours at 40 and MAX(0,…) keeps overtime from going negative in a short week. Overtime pay is 5.50 hours × $33.00 ($22.00 × 1.5) = $181.50. Gross pay is before income tax withholding, Social Security and Medicare, and any other deductions. In K13, =IF(AND(ROUND(G11*24-K6,2)=0,ROUND(K7+K8-K6,2)=0),"Ties","Check") confirms that the time total and the decimal hours agree and that regular plus overtime hours equal the total.

Excel overtime calculation: 40.00 regular hours, 5.50 overtime hours at 1.5 times $22.00, and gross pay of $1,061.50 with a check showing Ties. — 40.00 regular hours, 5.50 overtime hours and $1,061.50 gross pay.

Ready-to-share test: Change Friday's time out (F8) to 4:30 PM and enter a lunch from 12:00 PM to 12:30 PM in D8:E8. Friday becomes 8.00 hours, the week 48.50 hours with 8.50 overtime hours, gross pay $1,160.50, and K13 still shows Ties. Undo the changes when you are done.

State rules: Some states add their own overtime rules. California, for example, also requires overtime for hours over 8 in a workday. This template applies the federal 40-hours-per-workweek rule; add a daily calculation if your state requires one.

Where you will use this

Gross pay from the timesheet is the wages expense that flows into a monthly profit and loss statement, and staff costs are part of the fixed costs in a break-even analysis. Keep the timesheet next to an employee leave tracker so paid time off and hours worked are recorded in one place. With one row per shift for a whole team, SUMIFS between two dates totals each employee's hours for a pay period, and COUNTIFS with multiple criteria counts shifts by employee or type.

Common mistakes and quick fixes

What you see	Likely cause	Fix
`#####`	A negative time from an overnight shift	Wrap the subtraction in MOD(…,1).
Weekly total looks too low	The total cell uses h:mm, which resets at 24 hours	Use the custom format [h]:mm.
Pay is far too small	The time was multiplied by the rate without ×24	Use decimal hours: time × 24.
`#VALUE!`	A time was entered as text	Retype it with a space before AM or PM, or use TIMEVALUE.
A short break was deducted	Paid rest breaks were entered as breaks	Only enter unpaid meal breaks.
Overtime is wrong across two weeks	Overtime was worked out per pay period	Work out overtime separately for each workweek.

Frequently asked questions

What is the Excel formula for hours worked with a lunch break?

=(TimeOut-TimeIn)-(BreakEnd-BreakStart), formatted as [h]:mm. Multiply by 24 for decimal hours. Use =MOD(TimeOut-TimeIn,1)-MOD(BreakEnd-BreakStart,1) if any shift can cross midnight.

How do I handle two breaks in one shift?

Add a second pair of break columns and subtract both: =MOD(F4-C4,1)-MOD(E4-D4,1)-MOD(H4-G4,1), with the columns adjusted to your layout. Only unpaid meal breaks belong there.

Can I round time to the nearest 15 minutes?

Yes. =MROUND(C4,”0:15″) rounds a time to the nearest quarter hour. The US Department of Labor accepts rounding to the nearest 5, 10 or 15 minutes only if, over time, it does not shortchange employees, so round both up and down the same way.

Is overtime always 1.5 times the hourly rate?

Under the FLSA it is at least 1.5 times the regular rate. If an employee earns non-discretionary bonuses or works at more than one rate, the regular rate is a weighted figure, not just the hourly rate. Exempt salaried employees are not covered by these overtime rules.

Does this work in Google Sheets?

Yes. The formulas, MOD and the [h]:mm format (Format > Number > Custom number format) all work the same way in Google Sheets.

Final checklist

Times are real times, not text, and each row is one shift.
Only unpaid meal breaks are subtracted; paid rest breaks are not entered.
MOD(…,1) handles shifts and breaks that cross midnight.
The weekly total uses [h]:mm, and pay uses decimal hours (time × 24).
Overtime is worked out per workweek: MIN for regular hours, MAX for overtime.
The check cell shows Ties, and state rules on breaks and daily overtime have been reviewed.

For more HR templates, the employee leave tracker pairs well with this timesheet, and the monthly P&L shows where payroll lands in your reports.

Practice file: Download the timesheet with breaks workbook. The “How It Works” sheet lists every formula and what it does.

Run payroll prep in minutes, not hours

Browse the OneXcel Tutorial Hub for more step-by-step guides, explore ready-to-use Excel templates, or turn your hours and payroll data into a dashboard with the OneXcel Dashboard Builder.