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 this formula works
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:
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.
To check the weekday of a date, and roll back to Friday when the date is a Monday, you can use the IF and WEEKDAY functions. In the example shown, the formula in C5 is = IF ( WEEKDAY ( B5 ) = 2 , B5 - 3 , B5 ) How this formula works The WEEKDAY...
To return the next specific day of week (i.e. the next Wednesday, or Friday, or Monday) with a given start date, you can use a formula based on the WEEKDAY function. In the example shown, the formula in D6 is: = B6 + 7 - WEEKDAY ( B6 + 7 - 2 ) Where...
To get the last weekday in a month (i.e. the last Saturday, the last Friday, the last Monday, etc) you can use a formula based on the EOMONTH and WEEKDAY functions. In the example shown, the formula in D5 is: = EOMONTH ( B5 , 0 ) + 1 - WEEKDAY (...
To get the first day of the month for a given date, you can use a simple formula based on the DAY function. In the example shown, the formula in cell C5 is: = B5 - DAY ( B5 ) + 1 How this formula works The DAY function returns the day value for a...
The Excel WEEKDAY function takes a date and returns a number between 1-7 representing the day of week. By default, WEEKDAY returns 1 for Sunday and 7 for Saturday. You can use the WEEKDAY function inside other formulas to check the day of week...
Excel Formula Training
Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.