To calculate timesheet hours in Excel, subtract the clock-in time from the clock-out time, multiply by 24 to convert to decimal hours, and subtract any unpaid break minutes: =((C2-B2)*24)-(D2/60). For overnight shifts, wrap the difference in an IF that adds 1 when the clock-out time is earlier than the clock-in time, and use SUM to total a full week's worth of daily hours into a single weekly figure. Excel stores every time as a fraction of a 24-hour day, so 09:00 is actually 0.375 and 17:30 is 0.7291667 — the *24 multiplier turns that fraction back into the 8.5 hours you would expect on a timesheet. Without it, =(C2-B2) returns 0.3541667, which is mathematically correct but reads as a confusing decimal that breaks any later pay calculation. Once you build these formulas into a small seven-row table, one row per weekday from Monday through Sunday, the same pattern scales to any week and any pay schedule you need to track.
Setting Up a Timesheet Layout in Excel
Before any formula works, the columns need to be in place. Open a blank sheet and set up the headers and formats in this order:
- Label cells A1 through D1 with Day, Clock In, Clock Out, and Break (min).
- In column A, type the seven weekdays from Monday through Sunday, one per row down to A8.
- Select cells B2:C8 and apply the time format h:mm under Format Cells so every entry is read as a time value.
- Type clock-in and clock-out values in 24-hour format (09:00 for nine in the morning, 17:00 for five in the afternoon) into the rows you actually worked, leaving other rows blank.
- Format column D as a plain number and enter unpaid break minutes per day, again leaving blank rows empty.
Skip any day you did not work by leaving all three of its input cells blank; Excel treats them as zero, so the weekly total stays accurate without extra cleanup. Add an Hours column in E and a Notes column in F if you like, but the four inputs in B, C, and D are enough to drive every formula that follows. The reference cell for each row's hours is the matching cell in column E, which will hold the formula described in the next section.
The Core Formula: Daily Hours from Clock-In and Clock-Out
The single most important formula in any timesheet turns a clock-in and clock-out pair into a decimal number of hours. Place this in cell E2 for the Monday row:
=((C2-B2)*24)-(D2/60)
Excel reads the time difference C2-B2 as a fraction of a day, multiplies by 24 to scale it to hours, then subtracts the break minutes — divided by 60 to convert them into the same decimal-hour unit. A worked example with the Monday row filled as 09:00 in B2, 17:30 in C2, and 30 in D2 gives: ((17.5 - 9.0) × 24) - (30 ÷ 60) = 8.5 - 0.5 = 8.00 hours. Drag the formula down to fill cells E2 through E8 for the rest of the week; Excel automatically adjusts the row references so each row uses its own inputs.
Subtracting Unpaid Breaks and Handling Overnight Shifts
The break subtraction above works for any standard nine-to-five shift. The two real wrinkles in timesheet math are unpaid breaks that eat into the total and overnight shifts that cross midnight.
For a longer unpaid break, the formula still holds as long as the break is shorter than the shift itself — the day's total just drops accordingly. If the break is somehow longer than the shift, the formula returns a negative number, which is rarely what you want. Wrap the expression in MAX(0, ((C2-B2)*24)-(D2/60)) to clamp the day to zero instead of dipping below it. That single safeguard matches how payroll systems handle impossible inputs and keeps weekly totals from being dragged down by a single mistyped day.
For overnight shifts, the naive C2-B2 returns a negative fraction because 06:00 (0.25) is numerically smaller than 22:00 (0.9167). Fix it by detecting which time is larger and adding one full day when the shift wraps past midnight:
=IF(C2<B2, ((C2-B2+1)*24)-(D2/60), ((C2-B2)*24)-(D2/60))
Test this on a Tuesday row filled with 22:00, 06:00, and 0 break minutes: because 06:00 < 22:00, the IF takes the first branch and computes (0.25 - 0.9167 + 1) × 24 = 0.3333 × 24 = 8.00 hours, exactly the length of an eight-hour night shift. If the clock-in and clock-out times are identical, the result lands at 0.00 hours, which is the correct reading for a typo or a day off rather than a 24-hour marathon.
Summing a Full Week with SUM
With daily hours sitting in column E, the weekly total is a single SUM call. In cell E9 (or wherever you want the bottom line), type =SUM(E2:E8) and format the cell with two decimal places. The function skips blanks automatically, so a five-day work week with two empty rows just adds up the five filled cells, and a compressed four-day week still totals correctly without any IF gymnastics around the missing days.
If you also want an estimated weekly pay, place an hourly rate in a labelled cell like B11 and use =SUM(E2:E8)*$B$11. The dollar signs lock the rate reference when you copy the formula around. Times in this format work equally well for hourly invoicing, payroll stubs, and personal time-tracking, which is why the same four columns show up in almost every timesheet template you find online.
For a fuller worked walkthrough using the Mon/Tue/Wed rows above — 8.00, 8.00, and 4.00 hours respectively, with the remaining days left blank — the weekly total reads 8.00 + 8.00 + 4.00 = 20.00 hours. Multiply by a stored rate such as $25.00 and the same formula yields 20.00 × 25.00 = $500.00 in estimated pay. The arithmetic is shown in full so each step can be checked against the spreadsheet.
A Faster Browser-Based Alternative
Formulas work, but they also require you to set up columns, copy formulas down, format cells, and remember which reference means what. For most people checking a week's hours, that is overhead they would rather skip. The Timesheet Calculator does the same arithmetic on a single page: enter the clock-in, clock-out, and break minutes for each day in 24-hour format, and the daily hours, weekly total, and optional pay estimate appear immediately. Blank days are skipped automatically, overnight shifts are detected and rolled forward to the next morning, and break minutes are clamped to zero rather than producing negative numbers.
The tool also handles two extras that a hand-built spreadsheet does not get for free: an optional overtime rule where you set your own weekly threshold and multiplier (a common starting point of 40 hours at 1.5× is pre-filled but switched off by default), and a currency picker so the pay figure shows up with proper thousands separators. Everything runs locally in your browser using plain JavaScript, so times, breaks, and rates never leave your computer. For shift workers, freelancers reconciling billable hours, and small business owners preparing a single week's payroll, that speed is usually the deciding factor.
| Common timesheet task | Excel formula | What it does |
|---|---|---|
| Daily hours, no break | =(C2-B2)*24 | Subtracts clock-in from clock-out and scales the result to hours. |
| Daily hours minus break | =((C2-B2)*24)-(D2/60) | Subtracts unpaid break minutes from the daily total. |
| Overnight shift | =IF(C2<B2, ((C2-B2+1)*24)-(D2/60), ((C2-B2)*24)-(D2/60)) | Adds one day when the clock-out time is earlier than the clock-in time. |
| Clamp to zero | =MAX(0, ((C2-B2)*24)-(D2/60)) | Stops the day from going negative if the break exceeds the shift. |
| Weekly total | =SUM(E2:E8) | Adds all daily hours in the week. |
| Estimated weekly pay | =SUM(E2:E8)*$B$11 | Multiplies the weekly total by a stored hourly rate. |
When to Use Excel vs the Timesheet Calculator
Excel shines when you need a permanent, auditable record — a timesheet that lives inside a workbook alongside invoices, project hours, or a payroll journal. It also lets you build conditional logic (overtime tiers, shift differentials, holiday pay) that a one-page calculator cannot match. The guide to converting times to decimal hours covers the same *24 trick in more depth if your workbook grows beyond a simple seven-row table and you need to reuse the conversion across multiple sheets.
The Timesheet Calculator is the better choice when you want a fast answer with no setup, when you need to double-check a rota or a pay stub before signing off, or when you would rather not maintain a spreadsheet template at all. Because the calculations happen in your browser, you can run several what-if scenarios — different break lengths, an extra shift, a rate change — and watch the totals update instantly without retyping formulas. Each scenario you try stays private, since nothing is uploaded or stored between page loads.
Whichever path you take, the underlying math is the same: convert each time to minutes, subtract the unpaid break, add the days together, and apply the rate. Excel spells that out across columns; the Timesheet Calculator collapses it into a single fill-in form. Pick the version that fits the question you are trying to answer today, and switch tools freely as the task changes from a one-off check to a long-running payroll file.