Finding the week commencing of any given date can be useful at times. There isn’t a built in formula to do this in Excel but it’s pretty simple to achieve.
Lets say you have a date in cell A1 that is a Wednesday and you want to know the Monday of that week. You need to go back 2 days, right?
Excel’s WEEKDAY formula will turn any date into a number that corresponds with it’s day of the week.
In this case if you use =WEEKDAY(A1) Excel will give you the number 4 for a Wednesday. However, the WEEKDAY formula has a second argument so that you can specify how you want your week to start.
To make Wednesday a ‘2’ you need to use =WEEKDAY(A1,3)because…
1 or omitted returns numbers 1 for Sunday to 7 for Saturday.
2 returns numbers 1 for Monday to 7 for Sunday.
3 returns numbers 0 for Monday through 6 for Sunday.
So now you can take 2 away from your date to get back to Monday using the formula =A1-WEEKDAY(A1,3), pretty helpful hey!