How to extract all records between two dates in Excel?
Here is a range of data in Excel, and in this case, I want to extract all rows records between two dates as below screenshot shown, have you any ideas to quickly handle this job without looking for data and extracting them one by one manually?
To extract all records between two dates in Excel, you need to do as these:
1. Create a new sheet, Sheet2, and type the start date and end date in two cells, for instance, A1 and B1. See screenshot:
2. In the C1 in Sheet2, type this formula, =SUMPRODUCT((Sheet1!$A$2:$A$22>=A2)*(Sheet1!$A$2:$A$22<=B2)), press Enter key to count the total number of matching rows. See screenshot:
Note: in the formula, Sheet1 is the sheet contains the original data you want to extract from, $A$2:$A$22 is the range of data, A2 and B2 are the start date and end date.
3. Select a blank cell that you will place the extracted data, type this formula =IF(ROWS(A$5:A5)>$C$2,"",INDEX(Sheet1!A$2:A$22,SMALL(IF((Sheet1!$A$2:$A$22>=$A$2)*(Sheet1!$A$2:$A$22<=$B$2),ROW(Sheet1!A$2:A$22)-ROW(Sheet1!$A$2)+1),ROWS(A$5:A5)))), press Shift + Ctrl + Enter keys, and drag Auto fill handle over columns and rows to extract all data until blank cells or zero values appear. See screenshot:
4. Remove the zeros, and select the dates showing as 5-digit numbers, go to Home tab, and select Short Date in the General drop-down list to format them in date formatting. See screenshot:
Extract all records between two dates by Kutools for Excel
If you want to more easily handle this job, you can have a try on the Select Specific Cells feature of Kutools for Excel.
1. Select the data you want to extract from, click Kutools > Select > Select Specific Cells. See screenshot:
2. In the Select Specific Cells dialog, check Entire row option, and select Greater than and Less than from the drop-down lists, type the start date and end date into the text boxes, remember to check And. See screenshot:
3. Click Ok > OK. And the rows matched the dates have been selected. Press Ctrl + C to copy the rows, and select a blank cell and press Ctrl + V to paste it, see screenshot:
The Best Office Productivity Tools
Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%
Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
More than 300 powerful features. Supports Office/Excel
2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features
30-day free trial. 60-day money back guarantee.
"drag Auto fill handle over columns and rows to extract all data until blank cells or zero values appear."
This doesn't seem to be working for me. The first row gives me the dates but the same gets repeated in the next two rows.
Can you extend the formula to check for dates that encompass more than one day? For example, using the same start and end dates from 1/1/15-12/31/16, but the data in sheet 1 have start and end dates too rather than just existing for a singular date.
The trouble I am having because I need to find dates that run through the date range specified. So I want anything that runs through something like 1/2016 to 12/2017, however it needs to include start dates that may be before the range so I can identify the records I need to look at.