Get Monday of the week

Imagine you have any random date and want find the Monday of the week in which the date appears. You can see you will need to "roll back" a specific number of days, depending on what day of the week the given date is. If the date is a Wednesday, you need to roll back 2 days, if the date is a Friday, roll back 4 days, and so on, as seen in the table below:

How can we figure out the the roll back number?

It turns out that the WEEKDAY function, with a small adjustment, can give us the rollback number we need. WEEKDAY returns a number, normally 1-7 for each day of the week. By setting the optional second argument (return_type) to 3, WEEKDAY will return numbers 0-6 for a Monday-based week. In other words, we can use WEEKDAY to generate the roll back values in the table above for any given date. Perfect.

The formula simply exploits this behavior directly:

=B5-WEEKDAY(B5,3)
=25-Aug-2019-WEEKDAY(25-Aug-2019,3)
=25-Aug-2019-6
=19-Aug-2019

Monday of current week

To get the Monday of the current week, you can use this formula:

=TODAY()-WEEKDAY(TODAY(),3)

Here, we are using the TODAY function to inject the current date into the same formula. This formula will continue to update on an on-going basis.

Custom alternative

If you want to customize behavior based on the day of week, you use an alternative formula that uses the CHOOSE function with hard-coded adjustment values:

=B5-CHOOSE(WEEKDAY(B5,2),0,1,2,3,4,5,6)

This formula uses WEEKDAY to get an index for the day of week, and CHOOSE to fetch a roll back value. The advantage of this approach is that CHOOSE allows arbitrary values for each day of the week; you can customize as you like.