Note: The other languages of the website are Google-translated. Back to English
English English

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?

doc extract record between dates 1 shot arrow right doc extract record between dates 2

Extract all records between two dates by formulas

Extract all records between two dates by Kutools for Excelgood idea3


Extract all records between two dates by formulas


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:
doc extract record between dates 3

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:
doc extract record between dates 4

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:
doc extract record between dates 5

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:
doc extract record between dates 6


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.

Kutools for Excel, with more than 300 handy functions, makes your jobs more easier. 

After installing Kutools for Excel, please do as below:(Free Download Kutools for Excel Now!)

1. Select the data you want to extract from, click Kutools > Select > Select Specific Cells. See screenshot:
doc extract record between dates 7

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:
doc extract record between dates 8

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:
doc extract record between dates 9


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...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • 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.
kte tab 201905

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom
Comments (19)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
I recreated this example in Excel 2010, and the result was that the first row of data would show up, but everything underneath had a number error.
This comment was minimized by the moderator on the site
Have you pressed Shift + Ctrl + Enter keys?
This comment was minimized by the moderator on the site
I see what's wrong. You have to press the shift + CTRL + Enter keys while still in edit mode.
I was hitting those keys after entering the formula and hitting ENTER.
I wish it would have been clearer.
This comment was minimized by the moderator on the site
Yes, brian, after typing the formula and still in edit mode, directly press Shift + Ctrl + Enter keys (the keys instead of Enter key), to get the correct result.
This comment was minimized by the moderator on the site
Still doesnt work for me? I am doing exactly as above and still returning the NUM error? Any other ideas?
This comment was minimized by the moderator on the site
Same thing happens to me n Excel 2010. Something in the formula doesn't work correctly.
This comment was minimized by the moderator on the site
I used this code and it is pulling up dates outside of the window... how do I fix it?
This comment was minimized by the moderator on the site
Which code Kayla? Do you mean the formula?
This comment was minimized by the moderator on the site
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.
This comment was minimized by the moderator on the site
This formula worked brilliantly. I was concerned as I have multiple date duplicates and there was no mention of how it handles those; no issues. It returned all relevant data.
Stoked.
This comment was minimized by the moderator on the site
Thanks for your support, you can combined the duplicate dates cells after extracting.
This comment was minimized by the moderator on the site
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.
This comment was minimized by the moderator on the site
it worked well for single sheet. can u pls help me to get the same from multiple sheets of similar data. first 4 sheets contain similar data. i need all records of same 'date' in sheet5.
This comment was minimized by the moderator on the site
"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.
This comment was minimized by the moderator on the site
Hi, Neha, could you check if your auto calculate is turn on? Click Formulas > Calcuations Options > Automatic.
This comment was minimized by the moderator on the site
This formula works awesome thanks, I need some extra help though. I have 200 employees, each with certification that expires on different dates. The heading for each certification is at the top with their expiry dates below. I want to extract the rows that with all info if there is an expiry reached and throw them into a new sheet. Exactly like the example above just dated from multiple columns.
This comment was minimized by the moderator on the site
Hi, Christiaan82, could you upload the a screenshot about you workhsheet, or give me more details about the information of sheet format, only with your description, I do not get it clearly.
This comment was minimized by the moderator on the site
All sorted. Instead of trying to retrieve the rows from multiple columns, I added a new hidden sheet where all the info populates the info on top of each other. So the information only needs to be retrieved from one column, as per your example above.
Thank you Sunny
This comment was minimized by the moderator on the site
Hi, this formula worked ok, except it only returned one data set for each date in the range. Is there an adjustment to allow for multiple entries with the dame date value?
There are no comments posted here yet
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations