The reusable Excel timesheet formula is =MAX(0,MOD(ClockOut-ClockIn,1)*24-BreakMinutes/60), which returns daily net hours for a standard or overnight shift.
Excel stores clock times as fractions of a day, so subtracting two time cells gives elapsed days rather than elapsed hours. The formula converts that difference back into hours, handles a clock-out earlier than the clock-in, subtracts unpaid break minutes, and prevents a negative daily result. The rest of this guide builds a Monday-to-Sunday worksheet around the formula and then shows a separate browser-based way to check a weekly total.

Formula structure for each workday
Use a clear cell layout: clock-in in column B, clock-out in column C, unpaid break minutes in column D, and daily net hours in column E. Keeping source inputs separate from calculated values makes every row easier to audit and lets you copy the same formula down the sheet.
| Calculation | Formula pattern | Result |
|---|---|---|
| Daily net hours | =MAX(0,MOD(C2-B2,1)*24-D2/60) | Shift hours after unpaid breaks, with negative values reduced to zero |
| Weekly net hours | =SUM(E2:E8) | Total net hours from Monday through Sunday |
The MOD function wraps the time difference into one day. Multiplying by 24 converts the fraction of a day into hours. Dividing the break by 60 converts minutes into hours, while MAX keeps the final daily value at zero or above. Use cell references such as B2 and C2 unless you have defined the descriptive names used in the opening formula.
Build a reusable weekly timesheet in Excel
- Create the worksheet columns. In row 1, add Day, Clock In, Clock Out, Unpaid Break (min), and Net Hours. Enter Monday through Sunday in rows 2 through 8, and label row 9 as Weekly Total.
- Enter the clock values and breaks. Put actual time values in columns B and C, such as 09:00 and 17:00, rather than adding explanations to the same cells. Enter the unpaid break in column D as a plain number of minutes. Leave unused days blank; the daily formula will return zero for them.
- Add the daily formula. In cell E2, enter =MAX(0,MOD(C2-B2,1)*24-D2/60). Copy E2 down through E8 so each row refers to its own clock-in, clock-out, and break cells.
- Calculate the weekly total. In E9, enter =SUM(E2:E8). Format the daily and total cells as numbers with two decimal places so fractional hours are easy to read.
- Add a basic pay estimate. Put an hourly rate in H2, a currency label in H3, and the formula =E9*$H$2 in H4. Format H4 as currency using the currency required for the estimate.
- Test before entering the full week. Check one ordinary shift and one overnight shift against the daily totals. Also confirm that copied formulas reference the correct rows and that the total in E9 has not been typed over.
Check one overnight shift
Set B2 to 22:00, C2 to 06:00, and D2 to 30. The daily formula is =MAX(0,MOD(C2-B2,1)*24-D2/60), which substitutes as =MAX(0,(8/24)*24-30/60) and returns 7.50 net hours. This check confirms that the shift continues into the following morning and that the unpaid break is removed once.
Handle edge cases without manual corrections
- Overnight shifts: MOD turns a negative clock difference into the remaining portion of the day. A clock-out earlier than the clock-in is therefore treated as finishing the following morning.
- Identical start and finish times: The clock difference is zero, so the daily total is zero rather than a full 24 hours.
- Break longer than the shift: The subtraction could produce a negative value, but MAX returns zero for that day. The negative amount is not carried into the weekly sum.
- Blank non-working days: Rows can remain in the formula range. Blank inputs produce zero and can be skipped without deleting the row or entering artificial clock times.
- Times entered as text: Text-formatted clock values can cause arithmetic errors. Re-enter them as genuine Excel time values and apply a time display format to the cells.
- Shifts longer than one day: This clock-time pattern assumes the shift ends by the following morning. It is not designed to preserve extra full days in a shift lasting more than 24 hours.
Add weekly pay and a custom overtime estimate
Basic pay is the weekly net-hours total multiplied by the hourly rate. When E9 contains weekly hours and H2 contains the rate, =E9*$H$2 produces a direct estimate. Leave the rate cell empty if the worksheet should only track hours.
For a user-defined weekly overtime calculation, put the weekly hour threshold in H6 and the overtime multiplier in H7. Then use =MIN(E9,$H$6)*$H$2+MAX(0,E9-$H$6)*$H$2*$H$7 in H8. The first part applies the base rate to hours up to the threshold, while the second applies the selected multiplier to hours above it.
These formulas calculate only the rule you encode. They do not determine legal entitlement, daily overtime, payroll deductions, or other employment-specific treatment. Confirm the threshold, multiplier, and rounding method against the rules that apply to your contract and location before using an estimate for payroll.
Compare an Excel sheet with a browser calculator
When the goal is a one-off check rather than a reusable workbook, the Timesheet Calculator provides a separate browser workflow for clock-in, clock-out, breaks, weekly totals, and optional pay. It does not replace the worksheet when you need formulas that remain attached to editable cells.
| Need | Excel worksheet | Timesheet Calculator |
|---|---|---|
| Data entry | Custom columns for times, break minutes, and calculated net hours | Monday-through-Sunday rows with clock-in, clock-out, and break-minute inputs |
| Daily output | A copied formula that returns net hours in each daily row | Two-decimal net hours shown beside each completed day |
| Overnight work | MOD converts an earlier clock-out into a shift ending the next morning | An earlier clock-out is handled as finishing the next morning |
| Break over shift length | MAX reduces an excessive break adjustment to zero | A day with a break longer than the shift is counted as zero |
| Weekly total | SUM adds the daily net-hours cells | A running weekly total appears below the daily rows |
| Pay and overtime | You create and configure formulas and reference cells | Optional hourly rate, currency symbol, weekly threshold, and multiplier |
| Best fit | A reusable workbook with formulas and custom formatting | A quick browser-based total for one week |
Use the Timesheet Calculator for a separate weekly total
- Enter each working day. Open the Timesheet Calculator and enter the clock-in and clock-out times in standard 24-hour format, together with the number of unpaid break minutes. The calculator provides one row for every day from Monday through Sunday, and any blank day is simply skipped.
- Read the daily and weekly results. The two-decimal net hours appear beside each completed day, while the running weekly total appears below the table. Identical clock-in and clock-out times count as zero, and an earlier clock-out is treated as the next morning.
- Add optional pay information. Enter an hourly rate and choose a currency symbol to estimate total pay. The result is displayed with thousands separators and two decimal places. Leave the rate blank when only working hours are needed.
- Set overtime only when needed. Turn on the overtime rule, then choose the weekly threshold and multiplier. The rule begins switched off; once enabled, hours up to the threshold use the base rate and hours beyond it use the selected multiple.
- Adjust the schedule as needed. Change a time, break, rate, or overtime setting and the daily hours, weekly total, and pay estimate recalculate instantly in the browser.
All calculations run locally in the browser using plain JavaScript. The entered times, breaks, rate, and pay figures are not sent to a server, saved, or shared. The overtime option is convenience math rather than legal or payroll advice because applicable rules differ by country, state, and contract.
Run a final consistency check
- Compare every clock-in, clock-out, and break entry with the original work record.
- Confirm that each clock cell contains a real time value and each break cell contains minutes rather than a text label.
- Spot-check one daytime entry and one overnight entry before filling the remaining rows.
- Verify that the daily formula was copied through every working row and that the weekly total uses SUM over the intended range.
- Check that the pay formula points to the correct hourly-rate cell and that the displayed currency matches the estimate.
- Apply the relevant overtime and payroll rules separately when the result will be used for compensation records.
The Excel worksheet is the practical choice when formulas must remain reusable, editable, and visible in a workbook. The browser calculator serves a different task: producing a standalone weekly total quickly while keeping the entered schedule local to the browser.
For a deeper look, see How to Find Trapezium Area: Formula and Steps.
For a deeper look, see How to Find Weight on Other Planets: Formula and Steps.