2-Column Date Range Extract

[dcompto]

Using Excel 2010

(A)          (B)    (C)      (D)    (E)
[b]EMPLOYEE     ID     FROM     TO     EMAIL[/b]


Examples of Columns C and D:

[b]FROM           TO[/b]
01/01/12	01/17/12
01/18/12	02/29/12
01/18/12	02/29/12
01/18/12	02/29/12
03/01/12	05/31/12
03/01/12	05/31/12
09/01/11	05/31/12
09/01/11	05/31/12
09/01/11	01/15/12
09/01/11	01/15/12
09/01/11	01/15/12
01/16/12	05/31/12
01/16/12	05/31/12
09/01/11	05/31/12
09/01/11	05/31/12
09/01/11	05/31/12
09/01/11	05/31/12
01/15/12	05/31/12
09/01/11	05/31/12
07/01/11	12/31/11
07/01/11	07/31/11
09/01/11	09/30/11
07/01/11	07/31/11
08/01/11	12/31/11
07/01/11	08/31/11
08/08/11	03/31/12

How can I extract records that fall within a particular date range? For example, I want to find all records that fall within 01/01/2011-10/10/2011. This actually needs to be done every quarter.

 

[SkipVought]

hi,

Use MS Query faq68-5829

Or you could turn on the AutoFilter and use the date filters.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[dcompto]

I finally got what I needed. I placed Criteria dates in G2 and H2, then I placed the following formula in F2 and copied it down for all records:

=IF(OR(C5>=$G$2,D5>=$H$2),"Y","N")

I then filtered Column F for Y and copied the results to a new sheet.

I read the link provided by Skip, FAQ68-5829: Using MS Query to get data from Excel, and experimented for about an hour then gave up for the night. I will go back to it as soon as I have time because I want to understand how to use it.

 

[SkipVought]

Why not have the criteria in the AutoFilter for columns C & D.

Then no formulas required at all!




Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[dcompto]

I tried that last night. I never could get the right results. For column C, I used "is after or equal to" 10/1/2011. For column D, I used "is after or equal to" 10/31/2011. It only returned 57 records which is grossly incorrect. I didn't see a way to do an "OR" between two columns in the AutoFilter.

 

[randy700]

Perhaps, for column D, you should try "is BEFORE or equal to" 10/31/2011.


Randy

 

[SkipVought]

I wondered a similar thing that did not make sense to me, from the posted formula...
[tt]
=IF(OR(C5>=$G$2,D5>=$H$2),"Y","N")
[/tt]
where maybe it should have been...
[tt]
=IF(OR(C5>=$G$2,D5<=$H$2),"Y","N")
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[dcompto]

Tried that, too, and it returned ZERO records.

 

[dcompto]

Skip,

I wondered a similar thing that did not make sense to me, from the posted formula...

=IF(OR(C5>=$G$2,D5>=$H$2),"Y","N")

where maybe it should have been...

=IF(OR(C5>=$G$2,D5<=$H$2),"Y","N")

If I use D5<=$H$2, it returns Y instead of N (the opposite of what it should be). For example:

 FROM	      TO
08/22/11	09/30/11      Y
07/01/11	09/30/11      Y

 

[SkipVought]

I used your posted From To list

I turned on the AutoFilter

I set the COLUMN C filter to after or equal to 1/1/2011

I set the COLUMN D filter to before or equal to 10/10/2011

4 rows were displayed.

So what's the problem???



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[dcompto]

Beats me. If I test the same filters multiple times, I get different results even though I cleared the filters before each test:

COLUMN C filter = after or equal to 10/1/2011

COLUMN D filter = before or equal to 10/31/2011

 

[randy700]

The formula you entered is correct because you added two more columns (criteria dates).
However, Skip's suggestion of using filters removes the necessity for adding all those extra columns.
You simply need to filter the FROM to AFTER or equal and the TO column to BEFORE or equal. Similar to using BETWEEN two dates.
Filtering both columns to AFTER does no good.

Randy

 

[dcompto]

My apologies, especially to Skip:

Where did THAT come from???

I forgot that I used 01/01/2011-10/10/2011 in my original post and have since changed it to 10/1/2011-10/31/2011. I think I had better quit while I'm ahead. There's still something strange going on with my AutoFilter because it returns different results with the same tests even though I clear the filters at the beginning of each test.

Randy700,

However, Skip's suggestion of using filters removes the necessity for adding all those extra columns.
You simply need to filter the FROM to AFTER or equal and the TO column to BEFORE or equal. Similar to using BETWEEN two dates.
Filtering both columns to AFTER does no good.

I did exactly that and it did not return the correct number of records.

I don't want to waste any more of anyone's time. Thanks to all who responded. I really appreciate your help.