How to get the first day of the week for any date
✓ Verified in LibreOffice 25.8.7.3Snap any date back to its Monday (or Sunday) — for weekly grouping and reports.
The formula
| App | Formula | Notes |
|---|---|---|
| Excel | =A2-WEEKDAY(A2,2)+1 | Monday start. For Sunday start: =A2-WEEKDAY(A2,1)+1. |
| Google Sheets | =A2-WEEKDAY(A2,2)+1 | Identical. |
| LibreOffice Calc | =A2-WEEKDAY(A2,2)+1 | Identical. |
How it works
WEEKDAY mode 2 gives Monday=1 through Sunday=7 — exactly how many days into the week the date is — so subtracting it and adding 1 lands on that week's Monday: Wednesday July 22 → Monday July 20. Grouping data by this value (a helper column feeding SUMIF or a pivot) is the standard way to build weekly totals. The end of the week is the same result +6, and next Monday is +7.
Verified, not just documented
We ran =TEXT(DATE(2026,7,22)-WEEKDAY(DATE(2026,7,22),2)+1,"YYYY-MM-DD") in LibreOffice 25.8.7.3 (headless, with forced recalculation) and it returned 2026-07-20 — exactly the expected result. Every formula here is confirmed by actually executing it.