If range of dates contains weekend
This tutorial shows how to test if a range of dates contain at least Saturday or Sunday and return a value if the test is True or False through the use of an Excel formula, with the IF, SUMPRODUCT and WEEKDAY functions
|
=IF(SUMPRODUCT(--(WEEKDAY(B5:B8,1)=1)+(WEEKDAY(B5:B8,1)=7))>0,"Contains Weekend","No Weekend")
=IF(SUMPRODUCT(--(WEEKDAY(B9:B12,1)=1)+(WEEKDAY(B9:B12,1)=7))>0,$C$5,$C$6)
|
GENERIC FORMULA
=IF(SUMPRODUCT(--(WEEKDAY(rng,1)=1)+(WEEKDAY(rng,1)=7))>0,value_if_true,value_if_false)
ARGUMENTS GENERIC FORMULA
=IF(SUMPRODUCT(--(WEEKDAY(rng,1)=1)+(WEEKDAY(rng,1)=7))>0,value_if_true,value_if_false)
ARGUMENTS EXPLANATION This formula uses the IF, SUMPRODUCT and WEEKDAY functions to test if a range of dates has at least one day from a weekend (Saturday or Sunday) and return a value if the test is True or False.
Click on either the Hard Coded or Cell Reference button to view the formula that has the return values directly entered into the formula or referenced to specific cells.
In this example the formula identifies if at least one of the dates from a list of dates contains a weekend (Saturday or Sunday) through the use of the WEEKDAY and SUMPRODUCT functions. We have selected a return type of 1 for the WEEKDAY function, meaning if the WEEKDAY function returns a value of 1 or 7 it represents Sunday or Saturday, respectively. The SUMPRODUCT function is then used to sum the number of these occurrences. Then the IF function tests if there is at least one of these occurrences, meaning that there is at least one day from a weekend that is captured in the selected list. If the SUMPRODUCT function return a value of greater than zero the formula will return a text value of "Contains Weekend" otherwise it will return a text value of "No Weekend". |
| Related Topic | Description | Related Topic and Description |
|---|---|---|
| If a date falls on a weekend | How to test if a date falls on a weekend and return a value if the test is True or False | |
| If a date is equal to | How to test if a date is equal to another date and return a value if the test is True or False |
| Related Functions | Description | Related Functions and Description |
|---|---|---|
| IF Function | The Excel IF function performs a test on specified conditions entered into the formula and returns a specified value if the result is TRUE or another specified value if the result is FALSE | |
| WEEKDAY Function | The Excel WEEKDAY function returns a number between 1 and 7, representing the day of the week based on the return type that has been selected | |
| SUMPRODUCT Function | The Excel SUMPRODUCT function multiplies corresponding ranges and returns the sum of these values |