Get first Monday before any date
Generic formula
=date-WEEKDAY(date-2)
Related formulas
If Monday, roll back to Friday
Get next day of week
Get last weekday in month
Explanation
If you need to find the Monday immediately before any date you can use a generic formula that uses the WEEKDAY function.
How the formula works
If you imagine you have any random date and want to look back in time to find the nearest previous Monday, you can see that you will need to "drop back" a specific number of days, depending on the day of week (e.g. Mon, Tue, etc) of the date. If the date is a Monday, you'll need to drop back 7 days, if the date is a Tuesday, you'll need to drop back 1 day, and so on. The table below shows the adjustment needed for each day of the week.
| Date | Days to drop back |
| Monday | 7 |
| Tuesday | 1 |
| Wednesday | 2 |
| Thursday | 3 |
| Friday | 4 |
| Saturday | 5 |
| Sunday | 6 |
To calculate this adjustment dynamically for any date, you can use the fact that Monday is the 2nd day of the week in Excel's default scheme. Simply compute the day of week of the date 2 days earlier and use that result is the adjustment value. So, assuming a date of June 4, 2015 (a Thursday) in cell A1, the formula is solved as follows:
=A1-WEEKDAY(A1-2)=June 4,2015-WEEKDAY(June 4,2015-2)=June 4,2015-WEEKDAY(June 2,2015)=June 4,2015-3=June 1,2015
An alternative
If you find the weekday adjustment confusing and non-intuitive, you use an alternative formula that uses the CHOOSE function with hard-coded adjustment values:
=A1-CHOOSE(WEEKDAY(A1,2),7,1,2,3,4,5,6)
This version uses WEEKDAY to get an index for the day of week (with Monday as 1), then CHOOSE returns the correct number of days to drop back.
Monday previous to today
If you need to find the Monday previous to today, just use the TODAY function for date:
=TODAY()-WEEKDAY(TODAY()-2)