extracting a range of data between two date

[newton.rogers]

Details,
Column A: "In Dates" in the format dd/mm/yyyy
Column B: "Out Dates" in the same format
Column C: Calculates elapsed working days i.e using function NETWORKDAYS
All colums can have up to a years data which sometimes have a number of "In Dates" which have the same date and the same for the "Out Dates" i.e. the same dates make occur more than once.
The spreadsheet is used to keep track on a number documents coming in, being processed and then being sent out. We use this data to check numerous bits of information, how many documents we received, how long we took to process them and variuos other calculations such as average processing time, max & min processing time etc.
What I am trying to do is to be able to extract the data from column C for a certain period ie from a start date to an end date and then do the necessay calculations on the data extraced. Thanks - "Stumped

 

[jim may]

Check out Advanced Filter, under the Data Group

 

[MrVillareal]

Hi try this one for earlier version.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	In Date	Out Date	NetWorkdays		Start Date	End Date		In Date	Out Date	NetWorkdays
2	15/08/2011	18/08/2011	4		15/08/2011	25/08/2011		15/08/2011	18/08/2011	4
3	15/08/2011	22/08/2011	6					15/08/2011	22/08/2011	6
4	19/08/2011	29/08/2011	7					20/08/2011	25/08/2011	4
5	20/08/2011	25/08/2011	4
6	23/08/2011	01/09/2011	8
7	24/08/2011	27/08/2011	3
8	27/08/2011	05/09/2011	6
9	28/08/2011	02/09/2011	5
10	31/08/2011	04/09/2011	3
11	31/08/2011	06/09/2011	5
Sheet2

Excel 2010

Cell Formulas
Range	Formula
C2	=NETWORKDAYS(A2,B2)

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.

 

[MrVillareal]

and for 2010 version.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	In Date	Out Date	NetWorkdays		Start Date	End Date		In Date	Out Date	NetWorkdays
2	15/08/2011	18/08/2011	4		15/08/2011	25/08/2011		15/08/2011	18/08/2011	4
3	15/08/2011	22/08/2011	6					15/08/2011	22/08/2011	6
4	19/08/2011	29/08/2011	7					20/08/2011	25/08/2011	4
5	20/08/2011	25/08/2011	4
6	23/08/2011	01/09/2011	8
7	24/08/2011	27/08/2011	3
8	27/08/2011	05/09/2011	6
9	28/08/2011	02/09/2011	5
10	31/08/2011	04/09/2011	3
11	31/08/2011	06/09/2011	5
Sheet2

Excel 2010

Cell Formulas
Range	Formula
C2	=NETWORKDAYS(A2,B2)
H2	=IFERROR(INDEX(A$2:A$11,AGGREGATE(15,6,(ROW($A$2:$A$11)-ROW($A$2)+1)/(($A$2:$A$11>=$E$2)*($B$2:$B$11<=$F$2)),ROWS(H$2:H2))),"")

 

[newton.rogers]

I don't know if I am doing something wrong but all I get is the date 15/8/2011 in cell H2.
Secondly, I possibly didn't explain myself in the "Start" and "End" dates in cells E2 and F2, all I am wanting to do is extract the info based in the "In Date" column and does not have to look for dates in both the I
"In" and "Out Dates"
Please advise?

 

[jim may]

Did you try the Advanced Filter option?

 

[newton.rogers]

Sorry, youv'e lost me, I don't understand the advanced filter option, could you possibly explain?
Stumped

 

[MrVillareal]

Hi tnx for feedback. this is 2010 version. this does not require Ctrl Shift Enter.

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	InDate	OutDate	Networkdays		StartDate		InDate	OutDate	Networkdays
2	15/08/2011	22/08/2011	6		21/08/2011		24/08/2011	28/08/2011	3
3	19/08/2011	25/08/2011	5				29/08/2011	30/08/2011	2
4	24/08/2011	28/08/2011	3				01/09/2011	06/09/2011	4
5	29/08/2011	30/08/2011	2				06/09/2011	12/09/2011	5
6	01/09/2011	06/09/2011	4				11/09/2011	18/09/2011	5
7	06/09/2011	12/09/2011	5				15/09/2011	22/09/2011	6
8	11/09/2011	18/09/2011	5				20/09/2011	22/09/2011	3
9	15/09/2011	22/09/2011	6
10	20/09/2011	22/09/2011	3
Sheet3

Excel 2010

Cell Formulas
Range	Formula
G2	=IFERROR(INDEX(A$2:A$10,AGGREGATE(15,6,(ROW($A$2:$A$10)-ROW($A$2)+1)/($A$2:$A$10>=$E$2),ROWS(G$2:G2))),"")

 

[newton.rogers]

Thanks for that but I still require an end date, what I meant was that the "Start date" and "End date" must be based in the "In Date" column "A" and does not need to incorporate the dates in the "Out date" column.

 

[jim may]

Private Message me with your e-mail and I will send you an example File, using the Advanced Filter option.