Excel Magic Trick 894: Extract Records Between Two Dates: Filter or Formula
ฝัง
- เผยแพร่เมื่อ 29 มี.ค. 2012
- Download Excel Start File: people.highline.edu/mgirvin/Y...
Download workbook: people.highline.edu/mgirvin/Ex...
See how to : Extract Records Between Two Dates: Filter or Formula:
1. Static Data: Filter using Between Crteria and then copy and paste
2. Data that changes use formula for dynamic results that update instantly. See array formula that uses COUNTIFS, INDEX, SMALL ROWS, ROW and IF Functions.
3. See the the Excel 2007 function: IFERROR
4. See the 2010 Function AGGREGATE
5. See the Excel 2003 function COUNTIF
I am glad that you liked the video!
this video is amazing. although i was heading for another purpose, because of this clip, i turned to achieved something different yet impressive. i appreciate the effort you are paying Mr. Excelisfun!
This is brilliant. I have a change log for 10 customers. I used to have to filter to look for recent changes. Now it automatically shows changes from the last 7 days. Thank you SO MUCH!!
Hi there, very BIG thanks to you! I've been looking for this amazing magic trick for weeks. Here take my honor!
You are welcome!
I am glad that the video helped!
That's the excact thing I was looking for, thank you very much for video, it is tricky but when you explain it seems easy. Thanks again 😊😊😊
I want to thank you for making a download useable copy of this one!! I tried and tired to do the formula and it would not work so when I saw that you had it available you really made my day!!
Glad the video and files helped!!! Thanks for the support, Jennifer, with your comment, Thumbs Up and Sub : )
You're the best!!! Thanks for this!
Your sir, have made life easier!
Yes, I have a whole series of videos that teach the basics:
th-cam.com/play/PL3FBEE51974F03CCF.html
my file is not work howto solve
Hi. I want to create something like this but I want to create a list from a specific date and name. It will be a job sheet. So from the data table the jobs from that date assigned to a certain person can then be printed out as that persons job sheet for the day
ExcelIsFun is an wonderful channel. Its great.
Glad the channel helps! Thank you for the support with your comment, Thumbs Up and Sub : )
Hi +ExcelIsFun ,
Is there a way of generating this result without using array formulas as I need to generate a table of filtered data as in this video, however, the final table will hold over 12000 records and this method crashes excel.
Thanks,
Jack
Thanks, it worked like a charm :)
are just a human like us man! that was awesome!
Hello, thanks for sharing your Excel expertise with us TH-camrs. I am new to Excel and was wondering do you have any lessons for beginners? I have an Excel class coming up soon and want to be ready, as I heard that it was complex? Would appreciate it greatly. Again, Thanks!
Thank you for this great tutorial, I was trying to extract from table, but it seems doesn't work for me? Is there any way to extract from tables?
Cheers!
Thank you for the excellent tutorial. I'm practicing with the actual values on your table (including checking with f9) but the only good result I get is on cells E7, E8, and E9, the rest is #REF! Frustration is setting in, but I'll keep trying :-)
great video learnt a lot from this, have QUES.. how should I use this formula if the LOWER date is in 1 column, and the UPPER date is in another column,,
(I have a data base which has purchase date of an item and the sold date, and im trying to create closing stock report with that data base, is this possible?)
when you subtract row, isit the very first one or the one with the smallest value?
Thanks for this video, even though it isnt even widescreen yet. the skills of excels stays relevant.
Thank you Michael.
thank you for this!
you are amazing, how you use/manipulate different kind of function, I wish i could be like you
very informative video .. thanks heaps.
if I would like to use this method for a leave planner for example, where I have a start date and end date, and filtering is done per month, how do I change your formula to integrate an end date array as well? keep in mind that the dashboard would have to be filtered per month, and only list those employees.
i have in A column non consecutive dates so the lower and upper dates (e and f column)can fall in between the A column dates . i want to extract separate datavalue (c column) for each interval.
Hi, Thank you very much for this excellent tutorial. I simply followed the instructions and I could create what I wanted. But when I tried to create the table in another sheet within the same document, this was not working. The count appeared correctly as it happened in the same sheet. But the rows did not came properly. Only the first column (dates) came in order, but no other data were fetched to the new sheet. I tried many many times, but i could not succeed in it. Is there anything else to be done, if i want the table in a separate sheet? Can you please help me with it? Thanks in advance.
You are the best
Great video,can this formula be used if i've got two sheets 1st called expenses entered, 2nd called expenses by date, and in the expenses by date these are the headings in cel LOWER B3,UPPER B4 COUNT B5, DATE A6, DESCRIPTION B6, AMOUNT C6,?
can you give some tutorials on the same example here but includes subtotal for each region criteria and overall total for the whole region data. hope you could help out.
Awesome video, what if you wanted to search through a table of columns not rows?
Great and Fun!
Hi, the 'copy over' step you do at 6min57s does not work for my data! I have 2 columns which the dates are coupled, meaning that for a certain 'x' there is one and only one 'y' and I need to extract them both together. The extraction I am doing though is based only on the values of 1 columns ('x') but I would like to 'drag the extract value (formula) over' to the next cel in order to get the corresponding 'y'. Just like you it is done in the video ... find 'date' and drag it over to the next column to find the corresponding 'region' and 'sales'. When I try to do that, I only get the same 'x' values once again!
Has anybody gone through the same problem?
I have a data range table of 3 column; one is a min column; second is max column and the extract value; if the given value is in between the range the extract value needs to come out. how do i do that
This is a great formula and we are using it daily for 30.60.90 day reporting. The issue we have is that we built this out around an excel pivot table which contains 30,000 rows. Any ideas on what I can do to make the formula run quicker?
Is it possible to revert all extracted data back in original position anyhow by your formula please.
is it possible to use this formula to auto populate another sheet or does it have to be the same sheet?
Trying to run an analysis of teams doing patrols based of dates ranges. Sheet 1 being the master copy and then sheet 2-4 are the sheets I want specific data based on time frames to go to each.
Is it possible to get records between multiple dates? for example Set 1 date, Set 2 Date and..
Please advise i can't find the mentioned video workbook
Thanks in advance
Hi, I have a table with dates from and to and prices. These dates are chunky- consist of sometimes 2 months period for one price. I need to transfer these prices to another table where prices are listed week by week. What formula can I use to detect if the date range(or just first date) on my weekly table is within the range on my master table and then if it is insert the relevant price? Can you help please?
I have question hope you will guide
While using date you actually counted "date occurence". Suppose that there was a date in which no sale was made i.e an empty cell against a date. Now i select a date range in which the date with no sale also fall in. How will i calculate the number of "sales" in a date range not number of "dates in a date range??
How can you extract records between two dates if the dates are written on rows and extract values on columns?
Thanks a lot :)
Good Evening,
I need help with totaling the sales into one cell from another page. So, let's say that the data for the year is on a page called 2018 and I have a page called 2018 Jan Sales. On the data page West has 10 sales and MidWest has 5 sales. I need a formula that will pull the total amount of sales for January and put that total on the 2018 Jan Sales page for each Region. So, if each sale was for a $1 (for easy math), I need the 2018 Jan Sales Page to reflect that West had a total of $10 for January and MidWest had a total of $5 for January and so on. Can someone help me come up with a formula like that?
Thank you in advance.
Hi, thank you for detail video,
I need data to data comparison based on date can you share how to do it
Owesome.
Mike, I do hope everything's great with you and with your family.
Let me jsut thank you a lot and say that your formula it's so great!!
But,
I need to have something similar but, I need to look into two different sheets and extract, from both, data into a third master sheet, based on a cell value, and without blanks in the middle.
So, first things first:
1. I use excel for MAC 2011. So, no vstack, no filter, no unique functions.
2. I have a excel workbook, with 3 sheets.
3. sheet1 has information from one bank statement:
A1 - "date" | B1 - "client name" | C1 - "amount paid"
A2:A100 - dates
B2:B100 - client names
C2:C100 - amounts paid per client in those dates
4. sheet2 has information from the other bank statement:
A1 - "date" | B1 - "client name" | C1 - "amount paid"
A2:A100 - dates
B2:B100 - client names
C2:C100 - amounts paid per client in those dates
5. sheet3 must pull and gather the informations from both sheet1 & sheet2, based on one condition.
So:
A1 - will have a dropdown list with the names of the clients, that I can change whenever I want.
B1 - must have a formula that can look into both sheet1 & sheet2, and extract the oldest date between them based on that client name in cell A1.
C1 - must have a formula that can look into both sheet1 & sheet2, and extract the newest date between them based on that client name in cell A1.
A2 - "dates" | B2 - "client names" | C2 - "amout paid"
A2:A100 - must have a formula to drag down that can look into both sheet1 & sheet2, and extract all dates based on that client name in cell A1, and without blanks in the middle.
B2:B100 - must have a formula to drag down that can look into both sheet1 & sheet2, and extract all client names based on that client name in cell A1, and for the dates in column A, and without blanks in the middle.
C2:C100 - must have a formula to drag down that can look into both sheet1 & sheet2, and extract all amounts paid based on that client name in cell A1, and for the dates in column A, and without blanks in the middle.
Something like this:
Sheet1:
date client name amount paid
01-Dec-22 Bard 2 000,00
14-Jan-23 Rui 1 500,00
14-Jan-23 Bard 1 000,00
04-Apr-23 Paul 500,00
16-Sep-23 Paul 1 000,00
04-Dec-23 Bard 100,00
05-Dec-23 Bard 500,00
Sheet2:
date client name amount paid
10-Oct-22 Bard 500,00
01-Dec-22 Bard 500,00
14-Jan-23 Rui 500,00
05-May-23 Paul 500,00
20-Oct-23 Paul 100,00
31-Oct-23 Rui 1 300,00
23-Nov-23 Bard 200,00
07-Dec-23 Bard 200,00
10-Dec-23 Rui 100,00
11-Dec-23 Rui 500,00
12-Dec-23 Bard 200,00
And, Sheet3, it should be like bellow, when I choose, for instance, "Bard":
Bard 10-Oct-22 12-Dec-23
date client name amount paid
10-Oct-22 Bard 500,00
01-Dec-22 Bard 2 000,00
01-Dec-22 Bard 500,00
14-Jan-23 Bard 1 000,00
23-Nov-23 Bard 200,00
04-Dec-23 Bard 100,00
05-Dec-23 Bard 500,00
07-Dec-23 Bard 200,00
07-Dec-23 Bard 200,00
12-Dec-23 Bard 200,00
I have some formulas to get the oldest date from both sheet1 and sheet2 but it, somehow, just stop working, when I change the client name in cell A1 in sheet3:
={IFERROR(MIN(IF(Sheet1!B:B=A1;Sheet1!A:A;""));IFERROR(MIN(IF(Sheet2!B:B=A1;Sheet2!A:A;""));""))}
Somehow, it just stops working. And do not update when I change the value in cell A1 in sheet3.
So I'm looking to get, merge and organize all the info without blanks, for the rest of the rows in columns A, B & C in sheet3, based onn A1 cell value in sheet3.
Would you be able to help me with it?
If you prefer, I can send you the excel file. Just let me know where to.
Thank you so very much, Mike.
Kind regards.
Rui
I am sorry, I do not know Mac Excel. You can post question to: mrexcel.com/board
What if i want fo filter the date equal and after a certain value?
Hello, hope all is going well. Question --- is there any formula for copy & doing paste special the values from a list (stock figures) at the end of each month. Pls help. Thanks, Sandeep
How to use this formula with a table that is on another spreadsheet in the same workbook. I have a table called Promos but when I try to substitute A$4:A$12 with the table and column reference Promos[CloseDate] the formula is not working.
Dear
If i want to find records from based on four dates from two diferent columns of date, possible?
How do I apply multiple filters at Multiple Sheet Pivot? Is it like I have a Pivot and I have to use 3-4 filters to see a Data every time. Is there any way I can just store/template 3-4 filters and apply them at one go?
I am stuck.
I have a large spreadsheet that contains information entered for a contractor style business.
The spreadsheet has more information than just payroll.
I am attempting to creating a "pay stub sheet" to help speed up my data entry.
-Three varibles will determine the data on the pay stub sheet. (Start Date, End Date, Contractor Name)
-"Contractors Name" is in a drop down list.
-If contractor worked 9 days between the "Start/End Date" I need the 9 Days individual listed.
-If the contractor worked I need to extract the date, location and day rate per entry form the large spreadsheet.
Any help would be greatly appreciated.
great video! It is working but not at the end when I am adding =IF(R$4:R4)>$P$4;"";Index...... could you help me out on this??
I wish I'd been very close to where you live :(
I have copied the logic and it works great for one date example in E4.
But i have 100 years of data I need to sort through. The formula stops
working past the first range and year when copied. How can I get it to
extend to use multiple date ranges without manually changing the
formula? For example E4 4/1/2012 - F4 4-30/2012, then I want to keep
going E5 4/1/2013 - F5 4/30/2013. I feel like its a simple tweak but
i'm not able to figure it out.
Thank very much Sir, that was great!
But, why this formula does not work with my spread sheet.
Even, in the downloaded sheet, if I click F2 key and then press ENTER. The whole result gets upset?
Very Impressive work
Glad it helps, Bilal!!!
Hi Mike, enjoyed the video. I have a dilema...I created a daily on-call phone calls worksheet where, thanks to you, I've been able to get time stamps for all my calls on a daily basis for a pay period. (ie, 1st-15th n so on) ive been searching for a video that will help me extract the first stamp n last to compute daily hours on call. Can u help me or can I send you a copy of the worksheet. Hope to hear from u. Thank again.
Excelisfun is awesome channel, you can make database more then oracle by watching this channel ....
can this be done via a userform??
Thanks
Can we use ( upper)*( lower) ?
Thanks for the video. But couldn't help myself commenting that your voice sounds like David Schwimmer from Friends. :)
Question : How can i extract betwen numbers
for exmple from number -to number and open rows at the same
10350774 10350775 2
10639399 10639506 108
Can someone kindly help me please. I have been over and over the video and can't figure where I have gone wrong. I have extracted the rows correctly (date) and IIT has picked up all dates however when I putt it across it doesn't pull the other data, only the date again, all the way across. Please help I've just got a chart full of dates
If there are multiple transactions on the same date, how do you get it to display all unique transactions for that date?
Hi,
I have a question, what if the dates repeat in the date column.
Thanks
Vishvanath
With the formula, the repeated dates will be appears in the results as well. Try it...
i need help with my work using excel. can anybody help me with it? thank you...
I am using this formula in Excel 2016 and it was working fine until a few days ago. All of a sudden there is one column in my table that is not carrying its formula and formatting along to the next row. The first 4 columns of the table are populated from a user form. There are 2 columns that contain formulas with if() and vlookup() functions to find values elsewhere on the sheet and then there are 4 additional columns that perform mathematical calculations based on the values in the lookup cells. When a new record is inserted using the userform, the new row is created and the form data is entered as it should but the 6 calculated fields show #NA. On investigation, I discovered that the 1 st column with one of the lookup formulas is not carrying forward with the new row. I have gone so far as to delete the table and start from scratch but it didn't make any difference. Do you have any suggestions? I asked the question on a discussion form but no one was able to help.
Scratch my comment. I found the problem. It was in the userform.
How do you get the region and sales columns to update? Mine just copies over the date. (Date part works fine)
Same problem for me
You lost me at pressing F9, my F9 doesn't work, the keys are mapped differently, how do I get "k" the rows without pressing F9?
Thanks for Sharing
here you show extract data from starting date to end date & if I more elaborate formula by region so where I put formula for particular region.
kindly suggest.
Thanks
I am running into the same problem with 2016 Excel. I need Date Range, and Criteria in my case Stock price between an specific period for a particular ticker since my database has several stocks. Any thoughts on how to do it?
Hello, any chance to re upload the excel file? Thanks in advance.
Question: When I press Ctrl+Shift+Enter, it does not include the {} brackets after typing in the formula. I am then not able to drag the formula over into the other cells. It just keeps the formula that is in the original cell. I tried to manually enter in these brackets, but all it does is display the formula in the cell. I have Excel 2007. Please help! This is exactly what I need, and I am so close to getting it.
Brackets ca NOT be typed in. Must type correct formula, then use Ctrl + Shift + Enter.
ExcelIsFun I believe I have entered in the correct formula. I downloaded your workbook to double check. This is the formula I have: =IF(ROWS(J$2:J2)>$H$8,"",INDEX($A$2:$A$300,SMALL(IF($A$2:$A$300>=$H$2,IF($A$2:$A$300
This formula does require Ctrl + Shift + Enter to work properly. The formula posted here looks okay. I can't see a typo problem. I do not know what the problem is. Sorry.
I found your error after doing it myself and pulling my hair out... try this:
=IF(ROWS(J$2:J2)>$H$8,"",INDEX(A$2:A$300,SMALL(IF($A$2:$A$300>=$H$2,IF($A$2:$A$300
Dear excel is fun how to do this operation in Power query
How to use sumifs instead of counif
Hi this is very usefull.
I need one help i got only the first row the second rows are not getting populated and when i am doing CTRL+SHIFT+ENT then also i am nt getting the value Kindly help me on this.
=IF(ROWS(Sheet2!A$7:A7)>Sheet2!$B$4,"",INDEX(Sheet1!A$2:A$44,SMALL(IF(Sheet1!$A$2:$A$44>=Sheet2!$A$2,IF(Sheet1!$A$2:$A$44
why does it make a difference if there is an "
i face a problem. i am typing this code. error = "@!"...... but select all code then press F9 Showing Correct Answer. how to solve?
Why IFERROR not working here.....
hi, how do i extract records between two dates with a text string criteria? i need to add to this formula IF(ROWS(A$62:A65)>$A$4,"",INDEX(CONCEPTS!A:A,SMALL(IF(CONCEPTS!$A:$A>=$G$2,IF(CONCEPTS!$A:$A
Thank you , question is there any way that can find 3rd date between start and finish date with formula ?
i face a problem. i am typing this code. error = "@!"...... but select all code then press F9 Showing Correct Answer. how to solve?