Get the Monday of the week from a date
=A2-WEEKDAY(A2,3)Excel · result on the sample sheet: 6 rows (B2:B7)
Describe what you need and get a working WEEKDAY formula for Excel and Google Sheets, tested on real data before you copy it. WEEKDAY returns the day of the week for a date as a number.
Each formula below was calculated on its own sample sheet. Open one to see the sheet, why it works, and to try it yourself.
=A2-WEEKDAY(A2,3)Excel · result on the sample sheet: 6 rows (B2:B7)
=A2+7-WEEKDAY(A2)Excel · result on the sample sheet: 6 rows (B2:B7)
=A2-WEEKDAY(A2,3)-7Excel · result on the sample sheet: 5 rows (B2:B6)
=SUMPRODUCT(--(WEEKDAY(B2:B11,2)>5))Excel · result on the sample sheet: 4
=COUNTIFS($B$2:$B$13,">="&E2,$B$2:$B$13,"<"&E2+7)Excel · result on the sample sheet: 4 rows (F2:F5)
=SUMIFS($C$2:$C$11,$A$2:$A$11,">="&E2,$A$2:$A$11,"<"&E2+7)Excel · result on the sample sheet: 3 rows (F2:F4)
=INDEX($F$2:$F$8,WEEKDAY(A2,2))Excel · result on the sample sheet: 6 rows (B2:B7)
=SUMPRODUCT((TEXT($A$2:$A$13,"ddd")=D2)*$B$2:$B$13)Excel · result on the sample sheet: 7 rows (E2:E8)
You describe the task in plain English, and it writes the formula, using WEEKDAY where that's the right tool, for your table or a sample one. The formula is run in our engine in Excel and Google Sheets mode before you see it, so you get its result, not just a guess.
WEEKDAY returns the day of the week for a date as a number.
Yes. Each example below opens a page where you try the formula in a live grid and it's checked as you go, and the WEEKDAY function page has a practice exercise.