Note: In Excel’s default scheme, weeks begin on Sunday. However, this example assumes the first day of a week is Monday, configured with WEEKDAY’s second argument as explained 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:
Monday of current week
To get the Monday of the current week, you can use this formula: 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: 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.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
