If a date falls on a weekend
This tutorial shows how to test if a date falls on a weekend and return a value if the test is True or False through the use of an Excel formula, with the IF, OR and WEEKDAY functions
|
GENERIC FORMULA
=IF(OR(WEEKDAY(date,1)=1,WEEKDAY(date,1)=7),value_if_true,value_if_false)
ARGUMENTS GENERIC FORMULA
=IF(OR(WEEKDAY(date,1)=1,WEEKDAY(date,1)=7),value_if_true,value_if_false)
ARGUMENTS EXPLANATION This formula uses the IF, OR and WEEKDAY functions to test if a specific date falls on 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 a date falls on a weekend through the use of a WEEKDAY and OR 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. If the WEEKDAY function returns a value of either 1 or 7 the formula will return a text value of "Weekend" otherwise it will return a text value of "Weekday", through the use of the IF function. Two of the three dates in this example fall on a weekend and one falls on a weekday. |
| Related Topic | Description | Related Topic and Description |
|---|---|---|
| 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 | |
| OR Function | The Excel OR function performs a test on two or more conditions and returns a TRUE result if one of the conditions was met or a FALSE result if all of the specified conditions were not met |