Author: Oscar Cronquist Article last updated on February 16, 2018

Question: How to filter rows using dates?

Answer:

In this post I will describe how to:

Filter rows using array formulas

Array formula in cell D2:

=INDEX($A$2:$B$38, SMALL(IF(($A$2:$A$38=$H$1), MATCH(ROW($A$2:$A$38), ROW($A$2:$A$38)), ""), ROW(A1)), COLUMN(A1))

How to create an array formula

  1. Select cell D2
  2. Type the array formula
  3. Press and hold Ctrl + Shift
  4. Press Enter once
  5. Release all keys

How to copy array formula

  1. Select cell D2
  2. Copy cell D2 (Ctrl + c)
  3. Select cell D2:E9
  4. Paste (Ctrl + v)
How to remove #num errors
=IFERROR(INDEX($A$2:$B$38, SMALL(IF((A2:$A$38<$H$2)*(A2:$A$38>$H$1), MATCH(ROW(A2:$A$38), ROW(A2:$A$38)), ""), ROW(A1)), COLUMN(A1)),"")

Get excel sample file for this tutorial.

filter-records-between-two-dates.xls
(Excel 97-2003 Workbook *.xls)

Filter rows using an excel table

Convert range to a table

  1. Select range A2:B38
  2. Go to tab "Insert"
  3. Press with left mouse button on "Table"
  4. Press with left mouse button on OK!

Filter dates

  1. Press with left mouse button on Black arrow near Date header
  2. Hover over "Date filters"
  3. Press with left mouse button on "Between..."
  4. Select dates
  5. Press with left mouse button on OK!

Filter rows using excel table and vba

In this example, you can type a date in cell B1 and B2. Press the button and the table is instantly filtered. Copy the vba code below into a standard module. Create a button and assign the macro.

VBA code

Sub TblFilterDates()

Worksheets("Sheet3").ListObjects("Table13").Range.AutoFilter _
Field:=1, Criteria1:=">=" & Worksheets("Sheet3").Range("B1") _
, Operator:=xlAnd, Criteria2:="<=" & Worksheets("Sheet3").Range("B2")
End Sub

Where to copy vba code?

  1. Press Alt+F11
  2. Insert a new module
  3. Copy paste code into code window
How to create a button
  1. Press with left mouse button on "Developer" tab
  2. Press with left mouse button on "Insert" button
  3. Create a button (form control)
  4. Press with right mouse button on on the button
  5. Assign your macro

Get excel sample file for this tutorial.

filter-records-between-two-dates.xlsm
(Excel 2007 MacroEnabled Workbook *.xlsm)