If a date is not between two dates
This tutorial shows how to test if a specific date doesn't fall between two dates and return a value if the test is True or False through the use of an Excel formula, with the IF and OR functions
|
GENERIC FORMULA
=IF(OR(date<start_date,date>end_date),value_if_true,value_if_false)
ARGUMENTS GENERIC FORMULA
=IF(OR(date<start_date,date>end_date),value_if_true,value_if_false)
ARGUMENTS EXPLANATION This tutorial shows how to test if a specific date doesn't falls between two dates 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 initially identifies if a date is less than the start date or greater than the end date through the use of an OR function with the greater than (>) and less than (<) signs, which will identify if a date doesn't fall between the start and end dates. This is enclosed in the IF function to test if this is True and if so the formula will return a text value of "Outside", otherwise if the test is False the formula will return a text value of "Within". In this example we have only used one pair of start and end dates and applied them to all of the three dates we are testing. You can have different start and end dates for each of the dates that you are testing with different True and False return values. |
| Related Topic | Description | Related Topic and Description |
|---|---|---|
| If a date is between two dates | How to test if a specific date falls between two dates and return a value if the test is True or False | |
| Difference in months between two dates | How to calculate the difference in months between two dates |
| 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 | |
| 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 |