If a date is between two dates

This tutorial shows how to test if a specific date falls 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 AND functions

EXCEL FORMULA 1. If a date is between two dates

EXCEL

Hard coded formula
If a date is between two dates
Cell reference formula
If a date is between two dates
=IF(AND(B9>$C$5,B9<$C$6),"Within","Outside")
=IF(AND(B11>$C$5,B11<$C$6),$C$7,$C$8)
GENERIC FORMULA

=IF(AND(date>start_date,date<end_date),value_if_true,value_if_false)

ARGUMENTS
date: A date that you want to test if it's between two dates.
start_date: A start date that you want to test the date against.
end_date: An end date that you want to test the date against.
value_if_true: Value to be returned if the date is between the start and end date.
value_if_false: Value to be returned if the date does not fall between the start and end date.

GENERIC FORMULA

=IF(AND(date>start_date,date<end_date),value_if_true,value_if_false)

ARGUMENTS
date: A date that you want to test if it's between two dates.
start_date: A start date that you want to test the date against.
end_date: An end date that you want to test the date against.
value_if_true: Value to be returned if the date is between the start and end date.
value_if_false: Value to be returned if the date does not fall between the start and end date.

EXPLANATION

This tutorial shows how to test if a specific date 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 greater than the start date and less than the end date through the use of an AND function with the greater than (>) and less than (<) signs. This is enclosed in the IF function to test if this is True and if so the formula will return a text value of "Within", otherwise if the test is False the formula will return a text value of "Outside". 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 TOPICS

Related Topic Description Related Topic and Description
Difference in months between two dates How to calculate the difference in months between two dates

RELATED FUNCTIONS

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
AND Function The Excel AND function performs a test on two or more conditions and returns a TRUE result if all of the conditions were met or a FALSE result if one of the conditions was not met