Article updated on February 28, 2014

Question: How do I find out if a specific number is between two date ranges?

Answer: Yellow cells are user input cells. In this article example, number 9 is outside the range 2008-01-10 and 2008-01-15. See picture below.


Here is the array formula in cell E5.

=IF((INDEX($A$1:$A$15,MIN(IF(E4=$B$1:$B$15,ROW($B$1:$B$15),"")))<E2)*(INDEX($A$1:$A$15,MIN(IF(E4=$B$1:$B$15,ROW($B$1:$B$15),"")))>E1),"TRUE","FALSE") + Ctrl + Shift + Enter

Download excel sample file for this tutorial.find-out-if-a-number-is-between-two-dates
(Excel 97-2003 Workbook *.xls)

Functions in this article:

Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

returns the row number of a reference

Returns the smallest number in a set of values. Ignores logical values and text.