How to Pull Data from a Date Range in Excel: 7 Ways

The Sales Data in the table below; B4:D13 cells contain the Item, Date, and Sales columns respectively. It demonstrate how to pull data from a date range in Excel using various features, tools, and functions of Excel.

Dataset for how to pull data from a date range in excel

Method 1 – Using the FILTER Function


  • Go to cell B17 >> enter the formula below.

=FILTER(B5:D13,MONTH(C5:C13)=4,"No Info.")

Formula Breakdown
  • FILTER(B5:D13,MONTH(C5:C13)=4,”No Info.”) → filter a range or array. Here, B5:D13 is the array argument, while MONTH(C5:C13)=4 is the include argument that selects the values corresponding to the month of “April”. “No Info.” is the optional if_empty argument that is returned by the function if there are no matches.

Note: The FILTER function is available on Microsoft Excel 365, if you’re using an older version of Excel, then please check the second method.

how to pull data from a date range in excel using FILTER Function

Read More: How to Use IF Formula for Date Range in Excel

Method 2 – Combining INDEX, MATCH, SMALL, IF, ROW, and COLUMN Functions


  • Move to the B17 cell >> copy and paste the code below.

=IFERROR(INDEX($B$5:$D$13, SMALL(IF(($C$5:$C$13<=$G$5)*($C$5:$C$13>=$G$4), MATCH(ROW($B$5:$B$13), ROW($B$5:$B$13)), ""), ROW(A1)), COLUMN(A1)), "No Info.")

Formula Breakdown
  • COLUMN(A1) → returns the column number of a cell reference.
    • Output → 1
  • ROW(A1) → returns the serial number of a reference.
    • Output → 1
  • MATCH(ROW($B$5:$B$13), ROW($B$5:$B$13)) → returns the relative position of an item in an array matching the given value. Here, ROW($B$5:$B$13) is the lookup_value argument that refers to the “Item” column. Following, ROW($B$5:$B$13) represents the lookup_array argument from where the value is matched.
    • Output → {1;2;3;4;5;6;7;8;9}
  • IF(($C$5:$C$13<=$G$5)*($C$5:$C$13>=$G$4), MATCH(ROW($B$5:$B$13), ROW($B$5:$B$13)), “”) → checks whether a condition is met and returns one value if TRUE and another value if. Here, ($C$5:$C$13<=$G$5)*($C$5:$C$13>=$G$4) is the logical_test argument which prompts the IF function to return the value from MATCH(ROW($B$5:$B$13), ROW($B$5:$B$13)) (value_if_true argument) otherwise it returns blank “”  (value_if_false argument).
    • Output → {1;2;””;””;5;6;7;””;9}
  • SMALL(IF(($C$5:$C$13<=$G$5)*($C$5:$C$13>=$G$4),MATCH(ROW($B$5:$B$13), ROW($B$5:$B$13)), “”), ROW(A1))becomes
    •  SMALL({1;2;””;””;5;6;7;””;9}, 1) returns the kth smallest value in data set.
    • Output → {1}
  • INDEX($B$5:$D$13, SMALL(IF(($C$5:$C$13<=$G$5)*($C$5:$C$13>=$G$4), MATCH(ROW($B$5:$B$13), ROW($B$5:$B$13)), “”), ROW(A1)), COLUMN(A1))becomes
    • INDEX($B$5:$D$13, 1, 1)  → returns a value at the intersection of a row and column in a given range. In this expression, the $B$5:$D$13 is the array argument which is the “Item” column. Next, 1 is the row_num argument that indicates the row location, while 1 is the column_num argument that indicates the column location.
    • Output →”Playstation”
  • IFERROR(INDEX($B$5:$D$13, SMALL(IF(($C$5:$C$13<=$G$5)*($C$5:$C$13>=$G$4), MATCH(ROW($B$5:$B$13), ROW($B$5:$B$13)), “”), ROW(A1)), COLUMN(A1)), “No Info.”)becomes
    • IFERROR(“Playstation”, “No Info.”)  → returns value_if_error if the expression has an error and the value of the expression itself otherwise. Here, “Playstation” is the value argument, and “No Info.” is the value_if_error argument.
    • Output → “Playstation”

Combining INDEX, MATCH, SMALL, IF, ROW, and COLUMN Functions

  • Observe the steps in real-time in the GIF below to copy the formula into the cell below.

how to pull data from a date range in excel combining INDEX, MATCH, SMALL, IF, ROW, and COLUMN Functions

Read More: VLOOKUP Date Range and Return Value in Excel

Method 3 – Using the Date Filter Feature


  • Navigate to cell B16 >> click the Sort & Filter drop-down >> choose Filter.

Employing Date Filter Feature

  • Click the Down-arrow button >> jump to Date Filters >> select Custom Filter.

Using Custom Filter

  • Select the “Date is after 1/1/2022 and is before 3/31/2022” criteria >> hit OK.

Entering dates in Custom Autofilter

The results look like the image below.

how to pull data from a date range in excel employing Date Filter Feature

Method 4 – Utilizing Excel Table


  • Sarray B16:D25 array >> press the CTRL + T shortcut keys to insert Table.

Utilizing Excel Table

  • Press the Down-arrow button >> choose the months, in this case, “January”, “February”, and “March” >> click OK.

Filtering dates

The final table should look like the picture below.

how to pull data from a date range in excel utilizing Excel Table

Similar Readings

Method 5 – Implementing PivotTable


  • Move to the B4 cell >> click on Insert >> press the PivotTable button >> select the New Worksheet option >> hit OK.

Implementing PivotTable

  • Drag the “Item”, “Date”, and “Sales” Fields into the “Rows”, “Values”, and “Filter” Areas respectively.

Dragging PivotTable Fields

  • Press the Down-arrow button >> tick the Select Multiple Items box >> choose the dates according to your preference >> click OK.

Selecting Dates

The end results should look like the figure below.

how to pull data from a date range in excel implementing PivotTable

Method 6 – Using PowerQuery


  • Go to the B5 cell >> press CTRL + T keys to convert the dataset into a Table.

Using PowerQuery

  • Move to the Data tab >> choose From Table/Range option.

Selecting to Table or Range option

  • Follow the steps in the GIF for a live demonstration to filter the dated range.

Filtering specific dates in PowerQuery

  • Press the Close & Load drop-down >> choose the Close & Load To option.

Selecting Close & Load to option

  • Check the Existing worksheet button >> enter the B16 cell reference >> press OK.

Insertig Table into existing worksheet

Subsequently, the final output appears in the image given below.

how to pull data from a date range in excel using PowerQuery

Method 7 – Applying VBA Code


  • Navigate to the Developer tab >> click the Visual Basic button.

Applying VBA Code

  • Go to the Insert tab >> select Module.

Inserting Module

Copy the code below and paste it into the window as shown below.

Sub Extract_date_from_range()
    Dim Begin_date As Long, Finish_date As Long
    Begin_date = Range("G4").Value
    Finish_date = Range("G5").Value
    Range("C16:C25").AutoFilter field:=1, _
        Criteria1:=">=" & Begin_date, _
        Operator:=xlAnd, _
        Criteria2:="<=" & Finish_date
End Sub

VBA Code how to pull data from a date range in excel

Code Breakdown

The VBA code used to extract data from a date range.

  • The sub-routine is given a name, here it is Extract_date_from_range().
  • Define the variables Begin_date and Finish_date; assign the Long data type.
  • Use the Range.Value to set the cell references for the starting and ending dates, in this case, “1/1/2022” and “3/31/2022”.
  • Apply the Range.AutoFilter to filter the dates in between the starting and ending dates.

VBA code explanation

  • Click the Run button to execute the macro.

Running macro

The results should look like the screenshot below.

how to pull data from a date range in excel applying VBA Code

Read More: How to Use Formula for Past Due Date in Excel


Download Practice Workbook


<< Go Back to Date Range | Date-Time in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Eshrak Kader
Eshrak Kader

Eshrak Kader is a dedicated professional with a BSc. Degree in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology. He boasts a rich background in Microsoft Office Suite, and over the past year, he has authored over 120 insightful articles for the ExcelDemy. Currently, Eshrak leads a team of 5 in the ExcelDemy Video project and enjoys problem-solving and making videos on Excel. Eshrak’s passion for continuous learning underscores his commitment to excellence in... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF