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.
Your first month for $9.50 $19
50% off your first month when you join today.
Then $19/month. Cancel any time, and get a full refund within 14 days if it isn't for you.
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