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.
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.
Solve it on your own to keep the bonus. Each hint gets one step closer to the formula.
This is the grid you start with. Cell references in the task — B6, C2 — point at the row numbers and column letters below.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Date | Contractor | Hours | Week Commencing | Week Commencing | Hours Billed | |
| 2 | 10/27/2026 | Priya Shah | 8 | 10/26/2026 | |||
| 3 | 10/29/2026 | Marcus Lee | 7.5 | 11/2/2026 | |||
| 4 | 10/31/2026 | Priya Shah | 4 | ||||
| 5 | 11/1/2026 | Marcus Lee | 6 | ||||
| 6 | 11/2/2026 | Priya Shah | 8 | ||||
| 7 | 11/4/2026 | Marcus Lee | 8 | ||||
| 8 | 11/5/2026 | Marcus Lee | 6 | ||||
| 9 | 11/6/2026 | Priya Shah | 7.5 |
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.