The Monday that starts the week
PremiumIn B2, find the Monday of the week the order date in A2 falls in. Go back as many days as WEEKDAY(A2,2) says, then forward one: A2-WEEKDAY(A2,2)+1.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Order date | Week starting | ||||
| 2 | Mar 15, 2024 | |||||
| 3 | Mar 13, 2024 | |||||
| 4 | Mar 18, 2024 | |||||
| 5 | ||||||
| 6 | ||||||
| 7 | ||||||
| 8 |
Premium lesson
Unlock Week numbers and weekends and every other lesson
This is a premium lesson. Premium lets you solve it and every other lesson, take the timed mock tests, and earn certificates you can share on LinkedIn.
Next with premium:Loans: PMT and NPERInvestments: FV, NPV and IRR
Need help?
Why this works
Mar 15, 2024 is a Friday, so WEEKDAY(A2,2) is 5. Five days back is Sunday Mar 10, and one forward is Monday Mar 11. For a Monday, WEEKDAY is 1, so the formula returns the date itself. Group orders by this column and you have weekly totals.
Common mistake: Subtracting WEEKDAY without adding 1. That gives the Sunday before the week, not its Monday.
The answer
- B2
=A2-WEEKDAY(A2,2)+1
=A2-WEEKDAY(A2,2)+1 gives the Monday. For weeks that start on Sunday, use =A2-WEEKDAY(A2)+1.