Dates & Time
Get Most Recent Day of Week (Week-Starting Monday or Prior Friday)
Roll any transaction timestamp back to its week-starting Monday (inclusive on-or-before) or strictly prior weekday using B2 - MOD(WEEKDAY(B2) - target_dow + 7, 7).
Bucket daily transactions into consistent Monday-start weekly cohorts!
Weekly KPI dashboards, timesheets, and sprint burndown logs need every daily date mapped to its Week-Starting Monday (or Week-Beginning Sunday): Thursday 2026-10-29 should roll back to Monday 2026-10-26, while Monday 2026-10-26 itself should stay on 2026-10-26!
Subtracting MOD(WEEKDAY(B2) - Target_DOW + 7, 7) from B2 gives the Inclusive On-or-Before Floor Date (stepping back 0 days when B2 already lands on Target_DOW)! Need the Strictly Previous occurrence instead—so a Monday report pulls LAST Monday (7 days ago) rather than today? Simply shift B2 by 1 day before applying the exact same formula: (B2 - 1) - MOD(WEEKDAY(B2 - 1) - Target_DOW + 7, 7)!
| Transaction Date (B2) | Target DOW (2 = Monday) | Inclusive On-or-Before | Strictly Prior Occurrence |
|---|---|---|---|
| 2026-10-29 (Thursday) | 2 (Monday) | 2026-10-26 (3 days back) | 2026-10-26 (3 days back) |
| 2026-10-26 (Monday!) | 2 (Monday) | 2026-10-26 (Same day = 0 back) | 2026-10-19 (Full 7 days back!) |
| 2026-10-25 (Sunday) | 2 (Monday) | 2026-10-19 (6 days back) | 2026-10-19 (6 days back) |
Inclusive Most Recent Weekday (On or Before Date)
Maps any date B2 to the most recent target weekday B3 (1=Sun..7=Sat), keeping B2 unchanged if it already falls on B3.
=IF(OR(B3 < 1, B3 > 7), "Invalid Weekday (1..7)", TEXT(B2 - MOD(WEEKDAY(B2) - B3 + 7, 7), "yyyy-mm-dd"))Strictly Prior Weekday (Always at Least 1 to 7 Days Earlier)
Replaces B2 with B2 - 1 so a Monday query returns the previous Monday 7 days earlier.
=TEXT((B2 - 1) - MOD(WEEKDAY(B2 - 1) - B3 + 7, 7), "yyyy-mm-dd")Try the example
Use the original values or change them to test your understanding.