Get previous Monday, Sunday, or any most-recent weekday from a date

Universal "Week Of" date bucketing for cohort & pivot tables

To group daily sales into clean weekly buckets ("Week of 2026-05-04"), every date from Monday May 4 through Sunday May 10 must snap back to Monday May 4 (inclusive—so Monday itself stays May 4 rather than jumping back to April 27!).

Look at both patterns: (1) Week-Starting Monday: A2 - WEEKDAY(A2, 2) + 1 (since WEEKDAY(date, 2) numbers Monday=1..Sunday=7, Monday subtracts 0 days and Sunday subtracts 6 days!); (2) Universal Most-Recent Any-Weekday (inclusive) for ISO target day B2 (1=Mon..5=Fri..7=Sun): A2 - MOD(WEEKDAY(A2, 2) - B2, 7)!

Floor any date to Week-Starting Monday or universal target weekday B2

WEEKDAY(A2, 2) returns 1 for Monday through 7 for Sunday, making Monday cohort flooring a tiny arithmetic expression.

=IF(OR(NOT(ISNUMBER(A2)),NOT(ISNUMBER(B2)),B2<1,B2>7,MOD(B2,1)<>0),"Invalid date or weekday",TEXT(INT(A2) - WEEKDAY(INT(A2), 2) + 1, "yyyy-mm-dd"))

Universal most-recent weekday B2 (1=Mon..5=Fri..7=Sun, inclusive of same day)

For a strictly previous weekday, subtract one from the base date both outside and inside WEEKDAY: INT(A2)-1-MOD(WEEKDAY(INT(A2)-1,2)-B2,7). Subtracting one only inside WEEKDAY gives the wrong date.

INT removes the time fraction from numeric timestamps. These examples return formatted text; omit TEXT and format the numeric cell as a date when the result will feed other date calculations. B2 must be a whole number from1 to7. No holiday or timezone conversion is performed.

=IF(OR(NOT(ISNUMBER(A2)),NOT(ISNUMBER(B2)),B2<1,B2>7,MOD(B2,1)<>0),"Invalid date or weekday",TEXT(INT(A2) - MOD(WEEKDAY(INT(A2), 2) - B2, 7), "yyyy-mm-dd"))

Strictly previous weekday

For a valid numeric date and weekday1–7, this excludes the current date even when it matches.

=TEXT(INT(A2)-1-MOD(WEEKDAY(INT(A2)-1,2)-B2,7),"yyyy-mm-dd")

Further reading

• Microsoft Support — WEEKDAY function