date list extraction

[etch]

Okay this has me stuck

i have a list of date in a which has id, date and status (pass/fail) i want to be able to extract from the table all the data between two dates i.e. a week, if that is not possible then extract all data on a singular date. I used to be able to do this sort of thing in older versions of office, using ctirea and database commands, but i cannot seem to find what im looking for now.

 

[GregLocock]

Use the datevalue function. Cheers

Greg Locock

 

[melone]

Or try using a pivot table...

 

[etch]

to me the date value function is not the answer, all it seems to do is convert the date to a number, perhaps i made my question unclear.

I have a list of parts inspected, the parts are inputed on a day by day basis, on any typical day there will be 30 - 40 results. I want to be able to come along an extract all this data to another sheet for any given day, preferably a week. I know filters could do it, but its automating it and pulling it to another sheet thats the problem.

i know it would be easy in access but hey acess is for wimps lol, or more likley im dire at it.

 

[macajm]

Lets's assume your data is arranged as follows:-
A3 : Heading "Date"
B3 : Heading "DataValue"
A4 to A999 or whatever : actual dates
B4 to B999 or whatever : corresponding data values


Then in cell A1 enter the earliest required date,
and in cell A2 enter the last required date.

Then add an extra column C in your data table , with
C3 : Heading "SelectedData" and the following formula (replicated down for all rows of data)in cell
C4 : =if(and($A3>=$A$1,$A3<=$A$1),$B3,&quot;&quot

Now by using autofilter on the data list, you can just select &quot;NonBlanks&quot; for &quot;SelectedData&quot; in the filter criterion dropdown list, and only the data rows for the required data range will show, and you can then easily copy those rows to paste wherever you want.

 

[macajm]

Apologies - correction of formula, should be
=if(and($A3>=$A$1,$A3<=$A$2),$B3,&quot;&quot

 

[macajm]

Apologies - I shouldn't have rushed in - further correction of formula in C4, should be
=if(and($A3>=$A$1,$A3<=$A$2),$B4,&quot;&quot