Filter between 2 dates and blanks

[Nydia]

Hello all,

I have an excel spreadsheet that keeps tracks of request. Each month I have
to distribute this. It is a running list, but I only need to send the
requests completed for that month and any pending. So there is a date
column, which is labeled Date completed. If it was completed it will have a
date, if it is not completed and is pending it will be blank.

So, is there a way to filter between dates and blanks?

Example
between 10/01/08 and 10/31/08 and BLANKS

 

[TomPl]

This can be accomplished with <Data><Filter><Advanced Filter>.

The criteria range would look like this:

A B C
1 Date Date Blanks
2 >=10/1/2008 <=10/31/08
3 =ISBLANK(C15)

Where C15 is the first cell in your Date Column.

Assuming the above criteria are in cells A1:C3, you need to select exactly
that as your criteria range.

Good luck,

Tom

 

[Nydia]

All of the dates and blanks are in the G column. When I do advance filter it
does not allow me to add 1, 2, 3.

Can you please be alittle more specific on where to add this data?

 

[TomPl]

Nydia,

The best solution is to insert about five rows above your data. So assuming
rows 1 through 5 are blank, row 6 is column titles and the first date is in
cell G7. Also assume the column title in cell G6 is "Date".

In Cell A1 enter "Date" without the quotes. It must be the same as cell G6.
In Cell B1 enter "Date" without the quotes. It must be the same as cell G6.
In cell C1 enter "Blanks" without the qoutes.
In cell A2 enter ">=10/1/2008" without the quotes.
In cell B2 enter "<=10/31/2008" without the quotes.
In cell C3 enter "=isblank(G7)" without the quotes. Once entered it should
return "False" assuming G7 has a date.

Set the data range in Advanced Search to G6:G65000.
Set the criteria range in Advanced Search to A1:C3.

OK, that should do it.

Remember that Advanced Search is not volatile. To get it to update you must
click <Data><Filter><Advanced Filter><OK>.

The 1,2,3 in my previous post just represented row numbers and did not need
to be entered.

I hope this gets you going.

Once you get it working you can change the literals in cells A1 and B1 to
formulas to make it easier.

Tom

 

[Dave Peterson]

I'd use a helper column with a formula like:

=text(a2,"yyyymm")

And then filter to show both the blanks and 200810's.

 

[Dave Peterson]

ps. I meant I'd use autofilter, too.

Hello all,

I have an excel spreadsheet that keeps tracks of request. Each month I have
to distribute this. It is a running list, but I only need to send the
requests completed for that month and any pending. So there is a date
column, which is labeled Date completed. If it was completed it will have a
date, if it is not completed and is pending it will be blank.

So, is there a way to filter between dates and blanks?

Example
between 10/01/08 and 10/31/08 and BLANKS

 

[Nydia]

autofilter does not let me use the between 10/01/08 and 10/31/08 and blank.

 

[Dave Peterson]

Did you add the helper column?

autofilter does not let me use the between 10/01/08 and 10/31/08 and blank.

 

[Nydia]

when i do this, it only brings up one record.

 

[Dave Peterson]

pps.

I sometimes add a helper column and do all my logic in a formula:

=or(text(a1,"yyyymm")="200810",a1="")

And filter to show True/False.

 

[Nydia]

I'm lost. Can you please explain it from the begining.

I would put the helper column where and you have a2, 200810, why a2? then
do I do auto filter or advance filter.

 

[Nydia]

Cool, this seem to work. should Blanks=true?

Also, so I can understand, can you explain the formula for me

 

[Dave Peterson]

=or() looks for any argument to be true and it'll return true.

So if the date in a1 is in October of 2008 or the cell is empty, then you'll see
a true.

If both of those are not true, then you'll see a false.

Cool, this seem to work. should Blanks=true?

Also, so I can understand, can you explain the formula for me

 

[Nydia]

thank you sooo much for all your help

=or() looks for any argument to be true and it'll return true.

So if the date in a1 is in October of 2008 or the cell is empty, then you'll see
a true.

If both of those are not true, then you'll see a false.