Question: How to filter rows using dates?

filter-records-1

Answer: In D2:D38 i used this formula.

=IF(COUNT(IF((A2:A38<H2)*(A2:A38>H1)=0,"",ROW(A2:A38)-1))>=ROW()-1, INDEX(A2:A38,SMALL(IF((A2:A38<H2)*(A2:A38>H1)=0, "",ROW(A2:A38)-1),ROW()-1)),"") + Ctrl + Shift + Enter

SMALL(IF((A2:A38<H2)*(A2:A38>H1)=0, "",ROW(A2:A38)-1),ROW()-1) filters out the records that meet the criteria (A2:A38<H2)*(A2:A38>H1). If the criteria is met the row numbers are returned and then sorted.

INDEX(A2:A38,SMALL(IF((A2:A38<H2)*(A2:A38>H1)=0, "",ROW(A2:A38)-1),ROW()-1)),"") I can now get the dates using the row numbers and the Index function.

IF(COUNT(IF((A2:A38<H2)*(A2:A38>H1)=0,"",ROW(A2:A38)-1))>=ROW()-1, This part removes any #num errors.

In E2:E38 i used this formula. =IF(COUNT(IF((A2:A38<H2)*(A2:A38>H1)=0; "";ROW(A2:A38)-1))>=ROW()-1; INDEX(B2:B38;SMALL(IF((A2:A38<H2)*(A2:A38>H1)=0;"";ROW(A2:A38)-1);ROW()-1));"") The only difference from the formula in D2:D38 is ...INDEX(B2:B38;...

filter-records-2

Download excel sample file for this tutorial.
filter-records-between-two-dates
(Excel 97-2003 Workbook *.xls)

Functions used in this article:

IF(logical_test;[value_if:true];[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

SMALL(array,k) returns the k-th smallest row number in this data set.

ROW(reference) returns the rownumber of a reference

COUNT(value1;[value2])
Counts the number of cells in a range that contain numbers

INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

Share and Enjoy:
  • Share/Bookmark

Related posts:

  1. Count date records between two dates in a range in excel
  2. Count records between two dates in excel
  3. Count unique records by date in excel
  4. Most common value between two dates in a range in excel
  5. Sum values between two dates with criteria in excel
  6. Most common value between two dates in excel
  7. Find records using two numerical criteria in excel
  8. Count unique distinct records in a date range and a numeric range in excel
  9. Sort dates within a date range using excel array formula