To calculate a timesheet in Excel, use the formula =((C2-B2)*24)-D2/60 in each daily row, where B2 holds the clock-in time, C2 holds the clock-out time, and D2 holds the unpaid break in minutes, then wrap a SUM formula around the daily column to get the weekly total. That single formula chain handles most of the timesheets small businesses, freelancers, and hourly workers actually build — a week of normal day shifts, a paid or unpaid lunch break, and a clean total at the bottom. Excel stores every clock-in and clock-out as a fraction of a full 24-hour day, so the value 0.375 reads as 09:00 and 0.708333 reads as 17:00, which is exactly why multiplying the difference by 24 flips the result into decimal hours a payroll system can read. The challenge shows up the moment a shift crosses midnight, an employee works overtime, or you want a live pay estimate next to the hours — at which point a few extra formulas or a dedicated Timesheet Calculator start to make a real difference.
The Three Formulas Behind Every Excel Timesheet
Most homegrown Excel timesheets rely on three short formulas, one per column, repeated down each daily row. Once you understand the pattern, you can adapt it to any layout you want.
Gross hours: =(C2-B2)*24. This subtracts clock-out from clock-in, then multiplies by 24 because Excel stores time as a fraction of a day. The result is a decimal number such as 8.5 instead of a duration like 8:30.
Net hours after break: =((C2-B2)*24)-(D2/60). Add this column if you want to deduct unpaid break minutes. With break minutes stored in D2, dividing by 60 converts the break into the same decimal-hour unit as the gross hours.
Daily pay: =E2*$H$1, where E2 is the net hours cell and H1 holds the hourly rate. Format the result as currency to get a proper dollar sign and two decimals, and anchor the rate cell with $ so it does not shift when you fill the formula down.
At the bottom of the daily hours column, drop in =SUM(E2:E8) to get the weekly total, and =SUM(F2:F8) for the weekly pay cell. That is genuinely all the formula work a basic timesheet needs before you start adding overtime rules, rate changes, or shift differentials.
Building the Weekly Timesheet Layout Step by Step
- Open a blank worksheet and label column A "Day", B "Clock In", C "Clock Out", D "Break (min)", E "Hours", and F "Pay".
- List the seven weekdays in A2 through A8. Leave rows you do not need blank — they will simply add zero to the SUM at the bottom.
- Select B2:C8 and apply the cell format "Time" with the type code hh:mm so Excel accepts your entries as real times, not text.
- Type clock-in times in 24-hour format (09:00, not 9:00 AM) and clock-out times the same way. Twenty-four-hour input avoids the AM/PM trap that causes many timesheet mistakes.
- Enter break minutes as plain whole numbers in D2:D8, then format E2:E8 as Number with 2 decimals so the hours do not show as a time of day.
- In E2, enter =((C2-B2)*24)-(D2/60) and copy down to E8. In F2, enter =E2*$H$1 where H1 holds your hourly rate, then copy down to F8.
- Place =SUM(E2:E8) in the weekly total cell and =SUM(F2:F8) for the weekly pay cell. Format both as Number and Currency respectively.
- Save the file as a template (.xltx) if you want to reuse the layout week after week with only the time inputs changing.
Worked Example: A Single Day Through the Formulas
To make the math concrete, take one realistic row: clock-in 08:30, clock-out 17:00, unpaid lunch 30 minutes, hourly rate $18.00. Working through the formula chain step by step:
- Gross hours: (17:00 - 08:30) × 24 = 8.50 hours
- Net hours: 8.50 - (30 ÷ 60) = 8.50 - 0.50 = 8.00 hours
- Daily pay: 8.00 × $18.00 = $144.00
The same E2 cell formula produces 8.00 and the same F2 formula produces $144.00, so a full week of five identical days rolls up to 8.00 hours × 5 days = 40.00 hours and $144.00 × 5 days = $720.00 at the bottom — numbers you can verify instantly by changing the time inputs. For anything more complex than this, like variable daily hours, irregular breaks, or overnight shifts, build the rest of the week in the same template rather than trying to compute it on paper.
Common Excel Timesheet Problems and Quick Fixes
Even a clean timesheet can produce nonsense totals if a few setup steps are skipped. The table below lists the four problems that catch almost every first-time builder, along with the one-line fix for each.
| Problem | Why it happens | Fix |
|---|---|---|
| Hours show as 03:00 instead of 3.00 | Cell is formatted as Time, not Number | Format E2:E8 as Number with 2 decimals |
| Negative hours for an overnight shift | Clock-out is earlier than clock-in | Wrap with =IF(C2<B2,(C2-B2+1)*24-D2/60,(C2-B2)*24-D2/60) |
| Weekly total is wildly high | Break entered as hours instead of minutes | Convert break to minutes before dividing by 60 |
| Pay column shows $0.00 | Rate cell entered as text | Re-type the rate as a number, then re-fill the F column |
The overnight shift pattern is worth memorizing. When clock-out is earlier than clock-in, adding 1 to the time difference rolls the calculation past midnight, so a 22:00 to 06:00 shift becomes (06:00 + 1 - 22:00) × 24 = 8.00 hours instead of a negative number. The IF wrapper keeps the regular-day formula intact while routing overnight shifts through the adjusted math.
Another common frustration is overtime. Excel does not have a built-in overtime rule, so you have to add a second column such as =MAX(0, E2-8) to capture hours past 8 per day, then a third column for overtime pay at 1.5x. The math is straightforward, but every rule you add doubles the formula count and the chance of a typo.
Skip the Formulas: Try a Browser-Based Timesheet Calculator
Once your timesheet needs overtime rules, mixed rates, or a clean running weekly total, the formula chain gets long enough that a dedicated tool is faster. The Timesheet Calculator runs entirely in your browser, so nothing is uploaded or stored, and it handles every scenario from this article in plain inputs:
- One row per day from Monday through Sunday, with clock-in, clock-out, and break minutes in 24-hour format.
- Two-decimal net hours beside each daily row, with blank days skipped automatically.
- A running weekly total underneath the table that updates the moment any value changes.
- An optional hourly rate and currency symbol to estimate weekly pay with thousands separators and two decimals.
- An overtime toggle where you set the weekly threshold and the multiplier — a common default of 40 hours at 1.5x is pre-filled but switched off until you turn it on.
- Overnight shifts handled automatically, with results clamped to zero if a break exceeds the shift length.
The practical rule of thumb is simple. If your week is a handful of regular day shifts and a single rate, the Excel formulas above will do the job and the file stays yours forever. If your week crosses midnight, mixes overtime, or changes rates mid-week, copy your times into the Timesheet Calculator, read off the total, and use the spreadsheet for storage rather than arithmetic. That split — Excel for the log, the calculator for the math — is what most hourly workers and small payroll runs settle on after one or two frustrating formula debugging sessions.
If you're weighing options, Convert Text to Number Format in Excel: 5 Methods covers this in detail.