How to calculate hours per week from a timesheet in Excel

Step 1: hours for each shift

The log has one row per shift: date, employee, clock-in, clock-out and unpaid break, all typed as real times like 6:00 and 0:30. Excel stores a time as a fraction of a day, so 11:30 is 0.479. Every total below is in those day fractions until you multiply by 24.

Out minus in minus break works for day shifts. Ben's Sunday night shift on row 8 starts at 22:00 and ends at 6:30, so D8-C8-E8 gives -0.67 of a day, which a time format shows as ####. MOD(...,1) wraps a negative difference round the clock, so the same formula handles both. It assumes no shift runs 24 hours or longer.

Step 2: a week key for every shift

To group by week, every row needs the same label for the same week. A real date for the week start works best: =A2-WEEKDAY(A2,2)+1 returns the Monday on or before each date, because WEEKDAY with 2 numbers Monday as 1 and Sunday as 7. Format column G as a date.

Use your payroll week, not the calendar's. If your week runs Sunday to Saturday, use =A2-WEEKDAY(A2)+1; for a Saturday start, =A2-WEEKDAY(A2,16)+1.

An overnight shift belongs to the date it starts on. Ben's shift starting Sunday October 25 counts in the week of October 19 even though it ends on Monday. If your rules split hours at midnight, that needs two rows.

Step 3: total each person's week with SUMIFS

Build a small summary table: employee in I, week start in J, then SUMIFS adds the hours where both the name and the week match. Lock the log ranges with $ and leave I2 and J2 relative so you can fill down.

The week starts in J must be real dates equal to the ones in column G. A typed label like "Oct 19" matches nothing and the total is 0. Excel 365 users can build the two key columns with =UNIQUE(B2:B13) and the week dates, or skip the summary layout and use a PivotTable (below).

Prefer no helper column? Replace the week test with a date range: =SUMIFS($F$2:$F$13,$B$2:$B$13,I2,$A$2:$A$13,">="&J2,$A$2:$A$13,"<"&J2+7) gives the same 46:30.

Why the total shows 22:30: format as [h]:mm

Ana's 46.5 hours is 1.9375 days. With an h:mm format, Excel shows only the hours left after removing whole days, so the cell reads 22:30. It is the display that is wrong, not the value.

Select the totals and press Ctrl+1, choose Custom and type [h]:mm. The square brackets let the hours run past 24. Do the same for any grand total.

Step 4: decimal hours and overtime

Payroll and invoices need decimal hours. Multiply the time total by 24: =ROUND(K2*24,2) gives 46.5. Round at this step, after summing, so rounding differences do not pile up shift by shift.

Put the overtime threshold in an input cell (N2 holds 40 here as an example) because the rules depend on your contract and location. Then compare in the same unit. The trap: K2>N2 is FALSE for Ana, since 1.9375 days is never more than 40. Either convert the total to hours (K2*24) or the threshold to days (N2/24).

If overtime also applies per day, work it out on each shift row first and keep it out of the weekly figure, so the same hours are not paid twice.

No formulas: a PivotTable

If you only need the totals, a PivotTable builds the person-by-week grid for you and picks up new names automatically when you refresh. It still needs the Week of column, since the shift dates themselves would give one column per day.

1. Click inside the log and choose Insert > PivotTable, then OK.
2. Drag Employee to Rows, Week of to Columns and Hours to Values.
3. Click the Sum of Hours field, choose Value Field Settings > Number Format > Custom and type [h]:mm.
4. After adding shifts, use PivotTable Analyze > Refresh. Turning the log into a table first (Ctrl+T) makes new rows part of the source.