Skip to main content

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. 
Free Download
free full-featured in 30 days

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

Best Office Productivity Tools

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions…
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup  |   Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar   |  Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells   |  Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets:  12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...

Description

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!
Comments (19)
No ratings yet. Be the first to rate!
Megan
Megan
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?
Reply
0
0
Christiaan82
Christiaan82
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.
Reply
Report
0
0
Sunny
Sunny     Christiaan82
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.
Reply
Report
0
0
Christiaan82
Christiaan82     Sunny
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
Reply
Report
0
0
Neha
Neha
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.
Reply
0
0
Sunny
Sunny     Neha
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.
Reply
Report
0
0
Muneera
Muneera
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.
Reply
0
0
Tim
Tim
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.
Reply
0
0
Angry Savage
Angry Savage
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.
Reply
0
0
Sunny
Sunny     Angry Savage
This comment was minimized by the moderator on the site
Thanks for your support, you can combined the duplicate dates cells after extracting.
Reply
Report
0
0
Math
Math
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.
Reply
0
0
Kayla
Kayla
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?
Reply
0
0
Sunny
Sunny     Kayla
This comment was minimized by the moderator on the site
Which code Kayla? Do you mean the formula？
Reply
Report
0
0
Michelle
Michelle
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.
Reply
0
0
Brian
Brian     Michelle
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.
Reply
0
0
Sunny
Sunny     Michelle
This comment was minimized by the moderator on the site
Have you pressed Shift + Ctrl + Enter keys?
Reply
Report
0
0
brian
brian     Sunny
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.
Reply
0
0
Sunny
Sunny     brian
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.
Reply
Report
0
0
Jo Higgins
Jo Higgins     Sunny
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?
Reply
0
0
There are no comments posted here yet
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations