I don´t think I have explained how to filter dates outside date ranges.


Table 1 contains start and end dates for each date range.

Cell B13 is the start date and B14 is the end date. The array formula below filters all dates between the start and end date and outside the specified date ranges in table1.

Array formula in cell B16:

=IFERROR(SMALL(IF(COUNTIFS(Table1[Start:], "<="&$B$13+ROW($A$1:INDEX($A:$A, $B$14-$B$13))-1, Table1[End:], ">="&$B$13+ROW($A$1:INDEX($A:$A, $B$14-$B$13))-1), "", $B$13+ROW($A$1:INDEX($A:$A, $B$14-$B$13))-1), ROW(A1)), "")

How to create an array formula

  1. Copy above array formula
  2. Click in formula bar
  3. Paste array formula (Ctrl + v)
  4. Press and hold Ctrl + Shift
  5. Press Enter

How to copy array formula

  1. Select cell B16
  2. Copy cell (Ctrl + c)
  3. Select cell range B17:B25
  4. Paste (Ctrl + v)

Here is a picture where I have plotted date ranges. Red "x" are dates outside date ranges.

Download excel *.xlsx file

Filter dates outside date ranges.xlsx