Find data betwwen two dates

**parthmittal2007** · 02-22-2013, 09:05 AM

Hello, How all are doing today..
I am stuck with the issue and want's help

In Sheet1 I have a data some thing like

COL A----COLB----COLC
01234----DONE----01/01/2012
12344----NOT----02/21/2012
34323----DONE----03/15/2012
45678----NOT----01/13/2012
67890----DONE----04/22/2012
43434----NOT----09/01/2012
99999----DONE----02/01/2013

What I want to do is in sheet2 I should enter the two dates let's say start_date and end_date, It should give all the data between them
like say I enter 01/01/2012 and 04/22/2012, It should give

01234----DONE
12344----NOT
34323----DONE
45678----NOT
67890----DONE

Thanks and Regards
Parth Mittal

**Pepe Le Mokko** · 02-22-2013, 09:13 AM

Why not use the filter ?

**dilipandey** · 02-22-2013, 09:14 AM

HI Paarth,

Try using below formula:-

Formula:

Please Login or Register  to view this content.

see attached:- find data between 2 dates.xlsx

Regards,
DILIPandey
<click on below * if this helps>

**parthmittal2007** · 02-22-2013, 10:00 AM

Hi Attached is the excel sheet with proper requirement, please refer both sheet1 and sheet2 of the attached excel sheet

**parthmittal2007** · 02-22-2013, 10:41 AM

Any Idea or thought guys...

**Pepe Le Mokko** · 02-22-2013, 10:55 AM

Originally Posted by parthmittal2007

Any Idea or thought guys...

Please allow 24 hrs before bumping ( see forum rules)

**parthmittal2007** · 02-23-2013, 12:14 AM

COL A----COLB----Start_date-----End_date
01234----DONE----01/01/2012-----02/02/2012
12344----NOT----02/21/2012-----02/21/2012
34323----DONE----03/15/2012-----03/16/2012
45678----NOT----01/13/2012-----01/15/2012
67890----DONE----04/22/2012-----04/30/2012
43434----NOT----09/01/2012-----09/01/2012
99999----DONE----02/01/2013-----02/02/2013

What I want to do is in sheet2 I should enter the two dates let's say one start_date and one end_date, It should give all the data between them
like say I enter 01/01/2012 and 04/22/2012, It should give result is sheet2

COL A----COLB----Start_date-----End_date
01234----DONE----01/01/2012-----02/02/2012
12344----NOT----02/21/2012-----02/21/2012
34323----DONE----03/15/2012-----03/16/2012
45678----NOT----01/13/2012-----01/15/2012

Thanks

**dilipandey** · 02-23-2013, 07:14 AM

Hi Paarth, did you checked the attachment I posted in post # 3 ?

Regards,
DILIPandey
<click on below * if this helps>

**parthmittal2007** · 02-24-2013, 11:40 PM

But The Original requirement is posted in Post 7

Regards
Parth

**dilipandey** · 02-25-2013, 02:50 AM

If you see the attachment in my post #3, I guess you'll get what you asked in post #7 :-

What I want to do is in sheet2 I should enter the two dates let's say one start_date and one end_date, It should give all the data between them
like say I enter 01/01/2012 and 04/22/2012,

Earlier you had 2 columns and now you can have 4 columns with little modification in formula where you just need to change the bold part of the formula:-
IFERROR(INDEX(B$1:B$7

Best of luck

Regards,
DILIPandey
<click on below * if this helps>

**parthmittal2007** · 02-25-2013, 03:37 AM

Sorry I am not able to make it..I am new to it. Can you please give me the new consolodated formula

Thanks

**dilipandey** · 02-25-2013, 04:07 AM

Hi Paarth,

See the attached file where I used same formula with revised references

find data between 2 dates - rev.xlsx
Regards,
DILIPandey
<click on below * if this helps>

**parthmittal2007** · 02-25-2013, 06:07 AM

Thanks Dilip for your wonderful help, But one problem I am getting when I increase the data in sheet1, The result is not reflected in Sheet-2

Thanks

**dilipandey** · 02-25-2013, 06:23 AM

Edit formula and change row 8 to last row you have..

Regards,
DILIPandey
<click on below * if this helps>

**parthmittal2007** · 02-25-2013, 06:43 AM

Cannot I make it generic, I mean to say The formula is inserted on the sheet2 from row1 to row 200) and data can be vary ion sheet 1
Eg. Data can be there from row 1 to row 10 sometimes, or it can be increased.)
The formula in sheet2 always calculate whatever data is present.

currently I am using

Please Login or Register  to view this content.

**dilipandey** · 02-25-2013, 08:47 AM

Hi Parth,

See the below formula where I increased the range limit to 5000 rows :-

Formula:

Please Login or Register  to view this content.

Enter this formula using ctrl shift enter key combination.

see the attached file :-find data between 2 dates - rev(1).xlsx

Note:- you can go for dynamic defined name powered with offset function - but I will leave that for you to figure out and anyways, above formula can handle your data increase / decrease.

Regards,
DILIPandey
<click on below * if this helps>

**parthmittal2007** · 02-25-2013, 10:14 AM

Hello. Thanks for your support and patience.. Just want to know
Do Index changes If I move the formula from one excel file to another

**dilipandey** · 02-25-2013, 01:03 PM

Hi Parth, index will be ok if you put correct references

Just follow the data references in current sheet and ensure the same is there in another sheets / workbooks..

Regards,
DILIPandey
<click on below * if this helps>

**parthmittal2007** · 02-25-2013, 11:46 PM

In my current sheet the data start from A3. Please find the attached spread sheet for your reference

Thanks for your patience and post. I am really learning from it

**dilipandey** · 02-26-2013, 02:35 AM

Hi Parth,

Try using below formula:-

Formula:

Please Login or Register  to view this content.

Enter with key combination: ctrl shift enter

See attachment:- Datest.xlsx

you could have saved 'your' time if you would have shared this file earlier

Regards,
DILIPandey
<click on below * if this helps>

**parthmittal2007** · 02-26-2013, 05:14 AM

Hi I tried for the start date as 11/2/2012
and end date as 2/19/2013

It fails. According to the sheet It should give the whole data from sheet 1 but it don't

**dilipandey** · 02-26-2013, 06:29 AM

Okay.. because you have time as well along with 2/19/2013 in your first sheet.... Formula is looking until date and if even a minute is there, that will increase the date's value thus making it out of the condition.

revised the attachment as "< ClosingDate+1", see attached:-

Datest.xlsx

Regards,
DILIPandey
<click on below * if this helps>

**parthmittal2007** · 02-26-2013, 07:43 AM

Hi, Is there any index problem in the excel sheet
What you told me I have done for the single cell, Then I drag that cell to apply the formula to whole sheet.
But After that I am left with only one row in sheet 2,
Then I checked the formula, It is applied to whole cell. I am attaching the excel file for your reference.

**dilipandey** · 02-26-2013, 07:53 AM

You are not entering the formula with key combination: ctrl shift enter.. see the attachment in my post #22 where you 'll find { } brackets at both end of formulas which shows that they are array entered.

Regards,
DILIPandey
<click on below * if this helps>

**parthmittal2007** · 02-26-2013, 07:57 AM

So hoe to enter the key formula with array entered..

**parthmittal2007** · 02-26-2013, 08:17 AM

Hi
The Formula fails when I enter the
Start Date - 11/2/2012
End date - 2/2/2013

It the Shows the rows
33991266 DDDDD D 2/3/2013 2/3/2013 0.111388889 3.153333333 3.041944444 Y

Which should not be displayed.

If possible Can we do this with a Macro(If it can be made easier)

Thanks

**dilipandey** · 02-26-2013, 09:11 AM

Okay.. good catch.. now corrected.

Below formula is doing job .. enter with ctrl shift enter ke combination. Included bold part in the formula to cover end date till time 23:59:59

Formula:

Please Login or Register  to view this content.

see attached:- Datest(1).xlsx

Regards,
DILIPandey
<click on below * if this helps>

**parthmittal2007** · 03-01-2013, 08:08 AM

I Sincerely thanks dilipandey for its wonderful help and support.. Thanks once again..
My issue is resolved. This is a complete solution thread..

Cheers up..

**dilipandey** · 03-01-2013, 08:13 AM

You are welcome Parth..

cheers

Regards,
DILIPandey
<click on below * if this helps>

Find data betwwen two dates

LinkBack

Thread Tools

Rate This Thread

Display

Find data betwwen two dates

Re: Find data betwwen two dates

Re: Find data betwwen two dates

Re: Find data betwwen two dates

Re: Find data betwwen two dates

Re: Find data betwwen two dates

Re: Find data betwwen two dates

Re: Find data betwwen two dates

Re: Find data betwwen two dates

Re: Find data betwwen two dates

Re: Find data betwwen two dates

Re: Find data betwwen two dates

Re: Find data betwwen two dates

Re: Find data betwwen two dates

Re: Find data betwwen two dates

Re: Find data betwwen two dates

Re: Find data betwwen two dates

Re: Find data betwwen two dates

Re: Find data betwwen two dates

Re: Find data betwwen two dates

Re: Find data betwwen two dates

Re: Find data betwwen two dates

Re: Find data betwwen two dates

Re: Find data betwwen two dates

Re: Find data betwwen two dates

Re: Find data betwwen two dates

Re: Find data betwwen two dates

Re: Find data betwwen two dates

Re: Find data betwwen two dates

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions