Date & Time
Filter and Sum Records from the Last N Weeks (FILTER & SUMIFS Rolling Window)
Extract and total transactions falling within a rolling N-week lookback window anchored to a report cutoff date, excluding future-dated entries beyond the cutoff.
Bound both ends of a rolling N-week window [AsOf - 7*N + 1, AsOf]
A common rolling-window mistake is testing only Date >= AsOf - 7 * N, which accidentally includes future-dated scheduled orders or accruals after the report cutoff date and spans 7*N + 1 inclusive days instead of exactly 7*N days.
For an inclusive rolling window of exactly N weeks (7 * N calendar days ending on report date E2), the lower bound is E2 - 7 * F2 + 1 and the upper bound is E2. Pairing both bounds inside FILTER and SUMIFS keeps historical snapshots reproducible without volatile TODAY() drift.
| Order Date | Order ID | Revenue | In Last 2 Weeks (2026-05-08..2026-05-21)? |
|---|---|---|---|
| 2026-05-07 | ORD-401 | $1,200 | No (15 days ago — 1 day before window) |
| 2026-05-08 | ORD-402 | $850 | Yes (Exact start of 14-day window) |
| 2026-05-15 | ORD-403 | $1,450 | Yes (Inside window) |
| 2026-05-21 | ORD-404 | $600 | Yes (Anchor cutoff date) |
| 2026-05-25 | ORD-405 | $990 | No (Future post-cutoff entry) |
Dynamic FILTER for the Last N Weeks
Extracts Order IDs whose dates in A2:A6 fall inclusively within the 7*F2 days ending on E2.
=TEXTJOIN(", ", TRUE, FILTER(B2:B6, (A2:A6 >= E2 - 7 * F2 + 1) * (A2:A6 <= E2), "No Orders in Window"))Rolling N-Week Revenue Total (SUMIFS)
Sums revenue in C2:C6 across the exact same bounded [E2 - 7*F2 + 1, E2] window.
=SUMIFS(C2:C6, A2:A6, ">=" & (E2 - 7 * F2 + 1), A2:A6, "<=" & E2)Try the example
Use the original values or change them to test your understanding.