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

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

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" wrote:

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,

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

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

=text(a2,"yyyymm")

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



Nydia wrote:

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

--

Dave Peterson

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

Nydia wrote:

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

--

Dave Peterson

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



"Dave Peterson" wrote:

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

Nydia wrote:

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

--

Dave Peterson

Did you add the helper column?

Nydia wrote:

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

"Dave Peterson" wrote:

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

Nydia wrote:

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

--

Dave Peterson


--

Dave Peterson

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

"TomPl" wrote:

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

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 wrote:

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

"Dave Peterson" wrote:

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

Nydia wrote:

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

--

Dave Peterson


--

Dave Peterson

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.



"Dave Peterson" wrote:

Did you add the helper column?

Nydia wrote:

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

"Dave Peterson" wrote:

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

Nydia wrote:

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

--

Dave Peterson


--

Dave Peterson

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

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

"Dave Peterson" wrote:

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 wrote:

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

"Dave Peterson" wrote:

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

Nydia wrote:

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

--

Dave Peterson


--

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.

Nydia wrote:

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

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

"Dave Peterson" wrote:

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 wrote:

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

"Dave Peterson" wrote:

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

Nydia wrote:

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

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

thank you sooo much for all your help

"Dave Peterson" wrote:

=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.

Nydia wrote:

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

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

"Dave Peterson" wrote:

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 wrote:

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

"Dave Peterson" wrote:

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

Nydia wrote:

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

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson