Filter records between two dates
Question: How to filter rows using dates?
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;...
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
Related posts:
- Count date records between two dates in a range in excel
- Count records between two dates in excel
- Count unique records by date in excel
- Most common value between two dates in a range in excel
- Sum values between two dates with criteria in excel
- Most common value between two dates in excel
- Find records using two numerical criteria in excel
- Count unique distinct records in a date range and a numeric range in excel
- Sort dates within a date range using excel array formula




Leave a Reply