Date & Time
Sum timesheet hours by week and project (7-day window SUMIFS)
Roll up daily employee time entries into weekly project totals using half-open [WeekStart, WeekStart + 7) date criteria.
Why ">= WeekStart" and "< WeekStart + 7" beats WEEKNUM for weekly project billing
Grouping by a week number alone can combine different years. An explicit start date and an exclusive end date define an unambiguous seven-day window, including across year-end.
Use >=E2 and <E2+7 to include every timestamp on the last day while excluding the next week’s midnight. The example chooses Monday, but the formula accepts any chosen start date.
Use numeric date/time serials on a consistent local-time basis, a midnight start boundary and numeric decimal hours: 7.5 means seven and a half hours. Spreadsheet duration fractions require conversion to hours or a duration format instead.
The sum includes only the supplied rows. If the table combines multiple employees, add an employee criterion for per-person totals. This example does not calculate overtime entitlements or billing approval.
| Work Date (A) | Project Code (B) | Hours Logged (C) | Week of 2026-05-04 Match? |
|---|---|---|---|
| 2026-05-03 (Sun prior) | PRJ-Alpha | 4.0 hrs | Excluded (< May 4) |
| 2026-05-04 (Mon) | PRJ-Alpha | 7.5 hrs | Included! |
| 2026-05-07 (Thu) | PRJ-Beta | 6.0 hrs | Excluded (Project mismatch) |
| 2026-05-10 (Sun) | PRJ-Alpha | 5.0 hrs | Included! (Sum = 12.5 hrs) |
| 2026-05-11 (Next Mon) | PRJ-Alpha | 8.0 hrs | Excluded (Not < May 11) |
Weekly project hours rollup with half-open 7-day SUMIFS bounds
Using "<" & (E2 + 7) instead of "<=" & (E2 + 6) also protects you if timestamps include fractional clock times on Sunday afternoon!
=SUMIFS(C2:C6, A2:A6, ">=" & E2, A2:A6, "<" & (E2 + 7), B2:B6, F2)Total weekly hours across the supplied projects
Omit the project criterion to total all supplied rows within the same date window.
=SUMIFS(C2:C6, A2:A6, ">=" & E2, A2:A6, "<" & (E2 + 7))Try the example
Use the original values or change them to test your understanding.