Unquestionably, Microsoft Excel excels at crunching numbers! Now, this means you can perform tedious calculations, like filter date ranges, in the blink of an eye. In this regard, Excel has become a convenient and valuable tool. Keeping this in mind, this article demonstrates 7 useful ways how to pull data from a date range in Excel.
How to Pull Data from a Date Range in Excel: 7 Ways
To begin with, let’s suppose the Sales Data in the B4:D13 cells contain the “Item”, “Date”, and “Sales” columns respectively. Here, we want to demonstrate how to pull data from a date range in Excel using various features, tools, and functions of Excel. Henceforth, let’s see each method step-by-step.
Here, we have used the Microsoft Excel 365 version; you may use any other version at your convenience.
1. Using FILTER Function
Now, our first method needs no introduction since the title befittingly describes its action 😀. Here, we use the FILTER and MONTH functions to extract the required dates from the dataset.
📌 Steps:
- First and foremost, go to the B17 cell >> enter the formula given below.
=FILTER(B5:D13,MONTH(C5:C13)=4,"No Info.")
- 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”. Lastly, “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.
Read More: How to Use IF Formula for Date Range in Excel
2. Combining INDEX, MATCH, SMALL, IF, ROW, and COLUMN Functions
Alternatively, we can combine the IFERROR, INDEX, MATCH, SMALL, IF, ROW, and COLUMN functions to pull data from a date range in Excel. Better still, this method is compatible with older versions of Excel.
📌 Steps:
- First of all, move to the B17 cell >> copy and paste the expression shown 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.")
- 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”
- Afterward, observe the steps in real-time in the GIF below to copy the formula into the cell below.
Read More: VLOOKUP Date Range and Return Value in Excel
3. Employing Date Filter Feature
Besides, if using complex formulas doesn’t suit you then, the following methods can help you out. For instance, we‘ll employ the Date Filter option of Excel to extract the necessary dates.
📌 Steps:
- In the first place, navigate to the B16 cell >> click the Sort & Filter drop-down >> choose Filter.
- Next, click the Down-arrow button >> jump to Date Filters >> select Custom Filter.
- Later, select the “Date is after 1/1/2022 and is before 3/31/2022” criteria >> hit OK.
Finally, the results look like the image given below.
4. Utilizing Excel Table
For one thing, we can utilize the Excel Table option to pull data from a date range. Hence let’s see it in action.
📌 Steps:
- Initially, select the B16:D25 array >> press the CTRL + T shortcut keys to insert Table.
- Not long after, press the Down-arrow button >> choose the months, in this case, “January”, “February”, and “March” >> click on OK.
Lastly, the final output should look like the picture shown below.
Similar Readings
- How to Calculate Average If within Date Range in Excel
- How to Find Max Date in Range with Criteria in Excel
5. Implementing PivotTable
Conversely, Excel’s PivotTable feature allows us to sort and group data easily and effectively, so it’s no surprise, that we can apply PivotTable to filter a date range.
📌 Steps:
- At the very beginning, move to the B4 cell >> click on Insert >> press the PivotTable button >> select the New Worksheet option >> hit OK.
- Following this, drag the “Item”, “Date”, and “Sales” Fields into the “Rows”, “Values”, and “Filter” Areas respectively.
- At this point, press the Down-arrow button >> tick the Select Multiple Items box >> choose the dates according to your preference >> click on OK.
Consequently, the end results should resemble the figure shown below.
6. Using PowerQuery
In addition, the PowerQuery editor is another under-appreciated feature of Excel that can import, transform, and filter data from spreadsheets. So, let’s put this powerful tool to the test.
📌 Steps:
- To begin with, go to the B5 cell >> press CTRL + T keys to convert the dataset into a Table.
- Now, move to the Data tab >> choose From Table/Range option.
- Afterward, follow the steps in the GIF for a live demonstration to filter the dated range.
- Then, press the Close & Load drop-down >> choose the Close & Load To option.
- Furthermore, check the Existing worksheet button >> enter the B16 cell reference >> press OK.
Subsequently, the final output appears in the image given below.
7. Applying VBA Code
Last but not least, we can also apply VBA Code to pull data from a date range in Excel. Now, it’s simple and easy, so just follow along.
📌 Steps:
- First, navigate to the Developer tab >> click the Visual Basic button.
- Second, go to the Insert tab >> select Module.
For ease of reference, copy the code from here 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
Here, we’ll explain the VBA code used to extract data from a date range.
- In the first portion, the sub-routine is given a name, here it is Extract_date_from_range().
- Next, define the variables Begin_date and Finish_date; assign the Long data type.
- Then, use the Range.Value property to set the cell references for the starting and ending dates, in this case, “1/1/2022” and “3/31/2022”.
- In the second portion, apply the Range.AutoFilter method to filter the dates in between the starting and ending dates.
- Third, click the Run button to execute the macro.
Eventually, the results should look like the screenshot given below.
Read More: How to Use Formula for Past Due Date in Excel
Practice Section
We have provided a Practice section on the right side of each sheet so you can practice yourself. Please make sure to do it by yourself.
Conclusion
To sum up, we hope this tutorial has provided you with helpful knowledge on how to pull data from a date range in Excel. Now, we recommend you apply all this know-how in the practice dataset by downloading the practice workbook. In addition, feel free to comment and provide your valuable feedback.