Date FunctionsIntermediate

Group Timesheet Lines by Week with WEEKDAY

Agencies bill by the week, but timesheets arrive one day at a time. Subtracting WEEKDAY from a date walks it back to the Monday that week started on, and the return type you pick decides whether a Sunday shift lands in the right week or the next one.

Task:

You handle billing at a staffing agency, and the client is invoiced each week, Monday to Sunday. The timesheet in A2:C9 has one line per shift. In D2, write a formula that returns the Monday of the week each shift falls in, and copy it down to D9. Then in G2, total the hours billed for the week commencing in F2, and copy that down to G3.

Interactive Spreadsheet

Hints

+5 XPno-hints bonus

Solve it on your own to keep the bonus. Each hint gets one step closer to the formula.

The data in this exercise

This is the grid you start with. Cell references in the task — B6, C2 — point at the row numbers and column letters below.

9 rows × 7 columns10 cells you fill in
ABCDEFG
1DateContractorHoursWeek CommencingWeek CommencingHours Billed
210/27/2026Priya Shah810/26/2026
310/29/2026Marcus Lee7.511/2/2026
410/31/2026Priya Shah4
511/1/2026Marcus Lee6
611/2/2026Priya Shah8
711/4/2026Marcus Lee8
811/5/2026Marcus Lee6
911/6/2026Priya Shah7.5
What this exercise teachesMay contain the answer

A week commencing date is a label every line in that week can share, and WEEKDAY makes it by measuring how far each date sits from its Monday. Return type 3 numbers the days Monday 0 through Sunday 6, which is exactly that distance, so subtracting it leaves Monday where it is (11/2 stays 11/2) and walks every later day back to it. The shift that tests the formula is Marcus's Sunday on 11/1. The client's week runs Monday to Sunday, so it belongs to the week of 10/26. The default WEEKDAY counts Sunday as the first day of a new week, and the formula most people adapt from it, =A2-WEEKDAY(A2)+2, sends that Sunday forward to 11/2 instead. Nothing errors and every result looks like a Monday, but the first week bills 19.5 hours instead of 25.5 and the second 35.5 instead of 29.5, so one invoice goes out six hours short and the next six hours over. With the label in column D, SUMIF does the rest: it adds the hours on every line whose week matches F2, and because the weeks are worked out rather than typed, a late timesheet for any day lands in the right invoice on its own.