Exceljet

Quick, clean, and to the point

If date is between two dates

Excel formula: If date is between two dates
Generic formula 
=IF(AND(A1>=start,A1<=end),"x","")
Summary 

To test if a date is between two dates, you can use the IF function with the AND function. In the example shown, the formula in C5, copied down, is:

=IF(AND(B5>=start,B5<=end),"x","")

Where start (E5) and end (E8) are named ranges. As the formula is copied down, the formula returns "x" if the date in column B is between the start and end dates.

Explanation 

In this example, the goal is to check if a given date is between two other dates, labeled "Start" and "End" in the example shown. For convenience, both start (E5) and end (E8) are named ranges. If you prefer not to use named ranges, make sure you use absolute references for E5 and E8.

Excel dates

Excel dates are just large serial numbers and can be used in any numeric calculation or comparison. This means we can simply compare a date to another date with a logical operator like greater than or equal (>=) or less than or equal (<=).

AND function

The main task in this example is to construct the right logical test. The first comparison is against the start date. We want to check if the date in B5 is greater than or equal (>=) to the date in cell E5, which is the named range start:

=B5>=start

The second expression needs to check if the date in B5 is less than or equal (<=) to the end date in cell E5:

=B5<=end

The goal is to check both of these conditions are TRUE at once, and for that we use the AND function:

=AND(B5>=start,B5<=end) // returns TRUE or FALSE

The AND function will return TRUE when the date in B5 is greater than or equal to start AND less than equal to end. If either test fails, the AND function will return FALSE. We now have the logic we need to use with the IF function.

IF function

We start off by placing the expression above inside the IF function as the logical_test argument:

=IF(AND(B5>=start,B5<=end)

Next, we add a value_if_true argument. In this case, we  want to return an "x" when a date is between two dates, so we add "x" as a text value:

=IF(AND(B5>=start,B5<=end),"x"

If the date in B5 is not between start and end, we don't want to display anything, so we use an empty string ("") for value_if_false. The final formula in C5 is:

=IF(AND(B5>=start,B5<=end),"x","")

As the formula is copied down, the formula returns "x" if the date in column B is between the start and end date. If not, the formula returns an empty string (""), which looks like an empty cell in Excel. The values returned by the IF function can be customized as desired.

Author 
Dave Bruns

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.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.