How to get the first day of the week for any date
✓ Verified in LibreOffice 25.8.7.3 ✓ Verified in Google Sheets (2026-08-30)Snap any date back to its Monday (or Sunday) — for weekly grouping and reports.
The formula
| App | Formula | Notes |
|---|---|---|
| Excel (desktop) | =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. We then ran the same formulas in Google Sheets, executed 2026-08-30: a formula-only workbook goes into Google Drive, which converts it to a Sheet and recalculates every formula with Google’s own engine, and comes back out as .xlsx carrying the values Google computed. It returned 2026-07-20 for the worked example, the same value LibreOffice produced. Both engines’ numbers on this page are executed results. The Excel formula follows Microsoft’s official documented syntax — we do not run desktop Excel.
Functions used
WEEKDAY — see full Excel, Google Sheets & LibreOffice compatibility for each.