Get Monday of the week

To get the Monday of the week (i.e. the beginning of a week) for any given date, you can use a formula based on the WEEKDAY function. In the example shown, the formula in C6 is:

=B5-WEEKDAY(B5,3)

Note: In Excel's default scheme, weeks begin on Sunday. However, this example assumes the first day of a week is Monday, which is configured with WEEKDAY's second argument as explained below.

Explanation

Imagine you have a random date and want to 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:

Date	Rollback
Monday	0
Tuesday	1
Wednesday	2
Thursday	3
Friday	4
Saturday	5
Sunday	6

How can we calculate the rollback 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:

=WEEKDAY(A1,3) // start week at zero Mondays

This configuration allows us to use WEEKDAY to generate the rollback values in the table above for any given date. The formula 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 the 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 be updated on an ongoing basis.

Custom alternative

If you want to customize behavior based on the day of the 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 the week and CHOOSE to fetch a rollback value. The advantage of this approach is that CHOOSE allows arbitrary values for each day of the week so you can customize the behavior as you like.