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

We’ll use a simple sales dataset to demonstrate how you can pull data from a date range.

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

Method 1 – Using the FILTER Function

Steps:

Go to cell B17 and 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

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

Steps:

Move to the B17 cell and insert the following formula.

=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

Here’s a GIF overview.

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

Steps:

Navigate to cell B16, click the Sort & Filter drop-down, and choose Filter.

Employing Date Filter Feature

Click the Down-arrow button, jump to Date Filters, and select Custom Filter.

Using Custom Filter

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

Entering dates in Custom Autofilter

The results will look like the image below for the sample.

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

Method 4 – Utilizing an Excel Table

Steps:

Choose the B16:D25 array.
Press the CTRL + T shortcut keys to insert a Table.

Utilizing Excel Table

Press the Down-arrow button.
Choose the months “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 a PivotTable

Steps:

Go to the B4 cell.
Click on Insert, press the PivotTable button, select the New Worksheet option, and 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 on (All), tick the Select Multiple Items box, choose the dates you need, and 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

Steps:

Go to the B5 cell and press CTRL + T to convert the dataset into a Table.

Using PowerQuery

Move to the Data tab and choose From Table/Range.

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 and choose the Close & Load To option.

Selecting Close & Load to option

Check the Existing worksheet button, enter the B16 cell reference in the location box, and press OK.

Insertig Table into existing worksheet

Here’s the output in the sample.

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

Method 7 – Applying VBA Code

Steps:

Navigate to the Developer tab and click the Visual Basic button.

Applying VBA Code

Go to the Insert tab and 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.