Extract any data between 2 dates

[WillyBill]

Hi,

I have 4 columns, as you can see below (Excel)

[TABLE="width: 320"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD="align: left"]CustomerID[/TD]
[TD="align: left"] TransactionDate[/TD]
[TD="align: left"] Amount [/TD]
[TD="align: left"]Date of 1st purchase[/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]19/12/2016[/TD]
[TD]131.5[/TD]
[TD="align: right"]19/12/2016[/TD]
[/TR]
[TR]
[TD]1465[/TD]
[TD]24/12/2016[/TD]
[TD]73.44[/TD]
[TD="align: right"]24/12/2016[/TD]
[/TR]
[TR]
[TD]2389[/TD]
[TD]25/12/2016[/TD]
[TD]49[/TD]
[TD="align: right"]25/12/2016[/TD]
[/TR]
[TR]
[TD]367[/TD]
[TD]26/12/2016[/TD]
[TD]25.9[/TD]
[TD="align: right"]26/12/2016[/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]02/01/2017[/TD]
[TD]30[/TD]
[TD="align: right"]19/12/2016[/TD]
[/TR]
</tbody>[/TABLE]


I would like to extract any value in column 'Customer ID' that is between 2 dates from column 'date of 1st purchase'.
I am used to Googlequery (would take me 1 mn to do), but I need it on Excel, and I struggle to find a formula that says:

Select any data in A (customer ID), if D (date of 1st purchase) is greater than 01/12/2016 & smaller than 31/12/2016

Thanks in advance,
Looking forward to reading you soon,

 

[Special-K99]

With Customer ID in A1

Put 12/12/2016 in E1
Put 31/12/2016 in F1

in G2
=IFERROR(INDEX($A$2:$A$6,SMALL(IF(($D$2:$D$6>=E$1)*($D$2:$D$6<=F$1),ROW($A$2:$A$6)),ROW(A1))-(ROW(A$2)-1),1),"")
Array formula, use Ctrl-Shift-Enter
and copy down column G

 

[davidam]

Your problem is that excel does not recognize these numbers as dates. The way to make Excel happy with dates is to use mm/dd/yyyy. If you can switch the days and months, you will be able to do this easily.

 

[Special-K99]

Your problem is that excel does not recognize these numbers as dates. The way to make Excel happy with dates is to use mm/dd/yyyy. If you can switch the days and months, you will be able to do this easily.

Not true.
Those are European dates and will work if you have the regional setting set to Europe.
The OP doesn't even say he is having a problem with those dates.
If you read his description properly you will see he is simply asking to extract numbers, there is no mention of an existing problem.

 

[davidam]

Well, learn something every day. If Excel does recognize these as dates,then it should be a simple matter of If B* is greater that 'Date 1' and Less the Date 2 then....

 

[Special-K99]

I think you mean column D David.

As the OP didn't provide a result if the dates were outside that range I assumed the OP wanted a shortened list without blanks.

Re the dates, I doubt the OP has USA dates as he's manually typed two of them in European format.

 

[davidam]

You are probably right. Dates can be a major source of frustration in Excel, so I just jumped to that conclusion.

 

[WillyBill]

Hi,

Thanks so much, it works perfectly well and would have never found it by myself. And thanks for removing blank rows, very convenient as well.

Cheers