formula to filter data range

[jozhui]

Hi I have a range of data that I would like to filter using the current day's date and have all the lines that are greater than today's date show and any lines less than today's date be hidden. What formula will work?

 

[prasaddn]

Hi Jozhui,


Welcome to Chandoo.org.


If you like to see all the rows greater than today's date then why filter for current day's date? Instead filter for greater than or equal to current day's date.


In 2010 or 2007 version

This you can do by going to date column filter > date filter > Custom Filter > in prompted dialog box choose "Is after or Equal to" and next text field enter today's date (manually or using calendar).


Even if you are using 2003 version, you can do the same steps.


Regards,

Prasad DN

 

[jozhui]

Thanks. But say for instance I have a list such as below starting on row 3. I have the date on row one. I want to use that date in row 1 cell E1 to do a vlookup or a formula to look at the data range and only show me the rows that has a maturity date greater than Mar 14. How do I go about doing that? I have seen this before. Is it possible?

14-Mar


supplier Subsidiary Limit Effective Date Maturity Date

xyz a 10000 2/1/2011 2/1/2012

abc b 2000 3/1/2011 4/1/2012

efg c 300000 4/1/2011 6/1/2012

 

[NARAYANK991]

Hi ,


If you can use a helper column , say column F , then in cell F3 , put in the formula :


=E3-$A$1


and copy it down , as far as your data extends.


Now you can use a custom filter , specifying greater than 0 as a criterion.


Narayan