Filtering/Selecting Rows by Month Between Two Dates

[2Cool]

Hi All

Hope you can advise,suggest,help with my date filtering issue.

I have been given a spread sheet lovingly created by someone else which looks like a project management gantt chart.


Column 'A' contains the name of activities that take place over many weeks/months.

Column 'B' contains the start date of the activity.

Column 'C' contains the end date of the activity.

The columns 'D' onwards are headed with the start date of each week for the next 30 weeks and the cells below are color coded and some have text in as well for the different tasks of the activity in that row.


Columns A-C example

A-Activity B-StartDate C-EndDate

Activity WW 01/09/12 31/12/12

Activity XX 05/10/12 15/03/13

Activity YY 01/12/12 21/04/13

Activity ZZ 01/04/13 05/05/13

...etc

...etc


Question: Is it possible to filter these activity/rows for a specific month so only those activities/rows in which part or all of the activity is run are visible.

So if October is required only the first two rows would be visible or if March is required only the two middle rows would be visible.


I am using Excel 2010.


Thanks for any assistance with this.

 

[Hui]

2Cool


Firstly, Welcome to the Chandoo.org forums.


The answer is yes it can be done


Select the data including headings and goto Data, Filter


Select the Start_Date dropdown arrow and select Date Filters, Before

Is Before or Equal to 31/3/2013

Apply


Select the End_Date dropdown arrow and select Date Filters, After

Is After or Equal to 1/3/2013

Apply


Voila

 

[2Cool]

Hi Hui

Thanks! that works great.


I did find it a bit counter intuitive to use 'Is Before or Equal to' then select the last date of the period required in the Start Date filter and then 'Is After or Equal to' then select the first date of the period required in the End Date filter. I would think it would be the other way around. Main thing is it works


If I may - follow up question: is it possible to have the filter dates referenced/linked to two cells in the spread sheet. The final user of the spread sheet then only needs to enter dates for it to update instead of selecting and changing the drop down filters each time - or is that heading into VBA territory?.

 

[Hui]

2Cool


Unfortunately you can't link the Drop Down of the Filtered columns to cells


If you use Advanced Filter instead of Filter you can setup a Criteria Area where you define the criteria to do what you want


You can then invoke that manually or via a macro


First setup a Criteria Area

M11: StartDate (This Must match header name of Column B)

N11: EndDate (This Must match header name of Column C)

M12: '<31/3/2013

N12: '>1/3/2013


Select the data Area A1:C5

Remove the Filter, Data, Filter

Select Advanced Filter


Filter the List In Place - Enabled

List range: A1:C5

Criteria Range: M11:N12

Ok