Need to filter a list between two dates

drag-driver · Mar 1st, 2002, 07:56 AM

I haved tried using custom filter but this is not working. My project is a list which contains dates when an incident happened i need to find out how many incidents occured in a week, so i need to be able to split the dates over the range in to weekly lots.Any ideas would be greatly apreciated.

[ This Message was edited by: drag-driver on 2002-03-01 07:09 ]

Aladin Akyurek · Mar 1st, 2002, 08:32 AM

Quote:

On 2002-03-01 06:56, drag-driver wrote:
I haved tried using custom filter but this is not working. My project is a list which contains dates when an incident happened i need to find out how many incidents occured in a week, so i need to be able to split the dates over the range in to weekly lots.Any ideas would be greatly apreciated.

[ This Message was edited by: drag-driver on 2002-03-01 07:09 ]

Care to post some data?

If inclined to do so, activate an empty cell, type =, select 5 to 10 rows of relevant data including the labels, hit F9, copy what you see, and post it in the follow up along with expected results.

Note. You might want to have a look at PivotTables instead of a formula-based approach.

Aladin

Mark W. · Mar 1st, 2002, 09:00 AM

For a data field named 'Date'...

1. Enter a date from the week of interest into a cell D2.

2. Enter "Date" into cells E1:F1.

3. Enter the formula, =">="&TEXT(-WEEKDAY($D$2)+1+$D$2,"m/d/yy"), into cell E2.

4. Enter the formula, ="<="&TEXT(7-WEEKDAY($D$2)+$D$2,"m/d/yy"), into F2.

5. Use E1:F2 and your criteria range.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 1st, 2002, 07:56 AM	#1
drag-driver Board Regular Join Date: Feb 2002 Posts: 91	I haved tried using custom filter but this is not working. My project is a list which contains dates when an incident happened i need to find out how many incidents occured in a week, so i need to be able to split the dates over the range in to weekly lots.Any ideas would be greatly apreciated. [ This Message was edited by: drag-driver on 2002-03-01 07:09 ]

Mar 1st, 2002, 09:00 AM	#3
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	For a data field named 'Date'... 1. Enter a date from the week of interest into a cell D2. 2. Enter "Date" into cells E1:F1. 3. Enter the formula, =">="&TEXT(-WEEKDAY($D$2)+1+$D$2,"m/d/yy"), into cell E2. 4. Enter the formula, ="<="&TEXT(7-WEEKDAY($D$2)+$D$2,"m/d/yy"), into F2. 5. Use E1:F2 and your criteria range.