Using Autofilter to exclude dates
 

I'm working with data that has about 19,000 rows. The information is date
specific, and there are scores of rows for each date..
I was working with February data recently and was trying to use the
autofilter to exclude February 18 (President's Day) as in "Does not equal"
2/18/2008.

Much to my surprise it doesn't appear to work that way. It will exclude one
row of February data, but not all. I can use the "is less than" and "is
greater than" path, but it seems like a long way around. I thought I had used
the autofilter before to exclude all the rows of a specific date, but
apparently not. Any ideas out there?

Using Autofilter to exclude dates
 

Hmmm, it should work as you describe; I do this all the time and have
no problem.
Are your dates all stored as actual dates and not text?
I've just double-checked and it absolutely excludes 2/18/2008 when I
use "Does not equal 2/18" - note that the year is implied as the
current year and it will not hide 2/18/2007.
Please clarify.

On Jul 31, 1:15*pm, Ian wrote:
I'm working with data that has about 19,000 rows. The information is date
specific, and there are scores of rows for each date..
I was working with February data recently and was trying to use the
autofilter to exclude February 18 (President's Day) as in "Does not equal"
2/18/2008.

Much to my surprise it doesn't appear to work that way. It will exclude one
row of February data, but not all. I can use the "is less than" and "is
greater than" path, but it seems like a long way around. I thought I had used
the autofilter before to exclude all the rows of a specific date, but
apparently not. Any ideas out there?

Using Autofilter to exclude dates
 

The column in questions references another column that is a date/time stamp.
When I first noticed problem I thought that might be the issue.

So the workflow I employed was: first I entered the string

"=text (G14, "mm/dd/yy"

which made the cell information text.Then I did a copy on the column, then
"paste special-values" sequence followed by reformatting the cells as dates.
That was the best way I could figure to eliminate the time portion of the
stamp.

I'm sure I've successfully excludedthis before without going through all of
that, but I'm just trying to eliminate possibilites at this point.



"Reitanos" wrote:

Hmmm, it should work as you describe; I do this all the time and have
no problem.
Are your dates all stored as actual dates and not text?
I've just double-checked and it absolutely excludes 2/18/2008 when I
use "Does not equal 2/18" - note that the year is implied as the
current year and it will not hide 2/18/2007.
Please clarify.

On Jul 31, 1:15 pm, Ian wrote:
I'm working with data that has about 19,000 rows. The information is date
specific, and there are scores of rows for each date..
I was working with February data recently and was trying to use the
autofilter to exclude February 18 (President's Day) as in "Does not equal"
2/18/2008.

Much to my surprise it doesn't appear to work that way. It will exclude one
row of February data, but not all. I can use the "is less than" and "is
greater than" path, but it seems like a long way around. I thought I had used
the autofilter before to exclude all the rows of a specific date, but
apparently not. Any ideas out there?

Using Autofilter to exclude dates
 

Wouldn't =MOD(G14,1) have done the job, if all you wanted to do was get rid
of the time portion?
--
David Biddulph

"Ian" wrote in message
...
The column in questions references another column that is a date/time
stamp.
When I first noticed problem I thought that might be the issue.

So the workflow I employed was: first I entered the string

"=text (G14, "mm/dd/yy"

which made the cell information text.Then I did a copy on the column, then
"paste special-values" sequence followed by reformatting the cells as
dates.
That was the best way I could figure to eliminate the time portion of the
stamp.

I'm sure I've successfully excludedthis before without going through all
of
that, but I'm just trying to eliminate possibilites at this point.



"Reitanos" wrote:

Hmmm, it should work as you describe; I do this all the time and have
no problem.
Are your dates all stored as actual dates and not text?
I've just double-checked and it absolutely excludes 2/18/2008 when I
use "Does not equal 2/18" - note that the year is implied as the
current year and it will not hide 2/18/2007.
Please clarify.

On Jul 31, 1:15 pm, Ian wrote:
I'm working with data that has about 19,000 rows. The information is
date
specific, and there are scores of rows for each date..
I was working with February data recently and was trying to use the
autofilter to exclude February 18 (President's Day) as in "Does not
equal"
2/18/2008.

Much to my surprise it doesn't appear to work that way. It will exclude
one
row of February data, but not all. I can use the "is less than" and "is
greater than" path, but it seems like a long way around. I thought I
had used
the autofilter before to exclude all the rows of a specific date, but
apparently not. Any ideas out there?

Using Autofilter to exclude dates
 

I tried that but it set the date to 1/0/1900.

"David Biddulph" wrote:

Wouldn't =MOD(G14,1) have done the job, if all you wanted to do was get rid
of the time portion?
--
David Biddulph

"Ian" wrote in message
...
The column in questions references another column that is a date/time
stamp.
When I first noticed problem I thought that might be the issue.

So the workflow I employed was: first I entered the string

"=text (G14, "mm/dd/yy"

which made the cell information text.Then I did a copy on the column, then
"paste special-values" sequence followed by reformatting the cells as
dates.
That was the best way I could figure to eliminate the time portion of the
stamp.

I'm sure I've successfully excludedthis before without going through all
of
that, but I'm just trying to eliminate possibilites at this point.



"Reitanos" wrote:

Hmmm, it should work as you describe; I do this all the time and have
no problem.
Are your dates all stored as actual dates and not text?
I've just double-checked and it absolutely excludes 2/18/2008 when I
use "Does not equal 2/18" - note that the year is implied as the
current year and it will not hide 2/18/2007.
Please clarify.

On Jul 31, 1:15 pm, Ian wrote:
I'm working with data that has about 19,000 rows. The information is
date
specific, and there are scores of rows for each date..
I was working with February data recently and was trying to use the
autofilter to exclude February 18 (President's Day) as in "Does not
equal"
2/18/2008.

Much to my surprise it doesn't appear to work that way. It will exclude
one
row of February data, but not all. I can use the "is less than" and "is
greater than" path, but it seems like a long way around. I thought I
had used
the autofilter before to exclude all the rows of a specific date, but
apparently not. Any ideas out there?

Using Autofilter to exclude dates
 

Of course I got it wrong!
=MOD(G14,1) gives just the time.
=INT(G14) gives just the date part.
--
David Biddulph

"Ian" wrote in message
...
I tried that but it set the date to 1/0/1900.

"David Biddulph" wrote:

Wouldn't =MOD(G14,1) have done the job, if all you wanted to do was get
rid
of the time portion?
--
David Biddulph

"Ian" wrote in message
...
The column in questions references another column that is a date/time
stamp.
When I first noticed problem I thought that might be the issue.

So the workflow I employed was: first I entered the string

"=text (G14, "mm/dd/yy"

which made the cell information text.Then I did a copy on the column,
then
"paste special-values" sequence followed by reformatting the cells as
dates.
That was the best way I could figure to eliminate the time portion of
the
stamp.

I'm sure I've successfully excludedthis before without going through
all
of
that, but I'm just trying to eliminate possibilites at this point.



"Reitanos" wrote:

Hmmm, it should work as you describe; I do this all the time and have
no problem.
Are your dates all stored as actual dates and not text?
I've just double-checked and it absolutely excludes 2/18/2008 when I
use "Does not equal 2/18" - note that the year is implied as the
current year and it will not hide 2/18/2007.
Please clarify.

On Jul 31, 1:15 pm, Ian wrote:
I'm working with data that has about 19,000 rows. The information is
date
specific, and there are scores of rows for each date..
I was working with February data recently and was trying to use the
autofilter to exclude February 18 (President's Day) as in "Does not
equal"
2/18/2008.

Much to my surprise it doesn't appear to work that way. It will
exclude
one
row of February data, but not all. I can use the "is less than" and
"is
greater than" path, but it seems like a long way around. I thought I
had used
the autofilter before to exclude all the rows of a specific date,
but
apparently not. Any ideas out there?

Using Autofilter to exclude dates
 

Is there a reason that you're not just formatting it to LOOK
different?
Another way to get rid of the time is to use the TRUNC function to lop
it off.


On Jul 31, 3:20*pm, Ian wrote:
I tried that but it set the date to 1/0/1900.

"David Biddulph" wrote:
Wouldn't =MOD(G14,1) have done the job, if all you wanted to do was get rid
of the time portion?
--
David Biddulph

"Ian" wrote in message
...
The column in questions references another column that is a date/time
stamp.
When I first noticed problem I thought that might be the issue.

So the workflow I employed was: first I entered the string

"=text (G14, "mm/dd/yy"

which made the cell information text.Then I did a copy on the column, then
"paste special-values" sequence followed by reformatting the cells as
dates.
That was the best way I could figure to eliminate the time portion of the
stamp.

I'm sure I've successfully excludedthis before without going through all
of
that, but I'm just trying to eliminate possibilites at this point.

"Reitanos" wrote:

Hmmm, it should work as you describe; I do this all the time and have
no problem.
Are your dates all stored as actual dates and not text?
I've just double-checked and it absolutely excludes 2/18/2008 when I
use "Does not equal 2/18" - note that the year is implied as the
current year and it will not hide 2/18/2007.
Please clarify.

On Jul 31, 1:15 pm, Ian wrote:
I'm working with data that has about 19,000 rows. The information is
date
specific, and there are scores of rows for each date..
I was working with February data recently and was trying to use the
autofilter to exclude February 18 (President's Day) as in "Does not
equal"
2/18/2008.

Much to my surprise it doesn't appear to work that way. It will exclude
one
row of February data, but not all. I can use the "is less than" and "is
greater than" path, but it seems like a long way around. I thought I
had used
the autofilter before to exclude all the rows of a specific date, but
apparently not. Any ideas out there?

Using Autofilter to exclude dates
 

It appears that how the data is formatted is not the issue. I did get the
cells to reflect only the date (although I took the long way around) but I
still can't exclude the date in queston. It is helpful to know that you were
able to do it becaue I'm sure I've done it before on past data sets. This one
has me stumped.

Incidentally, when you excluded the date, did you have multiple rows that
had that particular date and it exluded all of them? It does appear to
exclude one row of the date, but that's not really helpful.


"Reitanos" wrote:

Is there a reason that you're not just formatting it to LOOK
different?
Another way to get rid of the time is to use the TRUNC function to lop
it off.


On Jul 31, 3:20 pm, Ian wrote:
I tried that but it set the date to 1/0/1900.

"David Biddulph" wrote:
Wouldn't =MOD(G14,1) have done the job, if all you wanted to do was get rid
of the time portion?
--
David Biddulph

"Ian" wrote in message
...
The column in questions references another column that is a date/time
stamp.
When I first noticed problem I thought that might be the issue.

So the workflow I employed was: first I entered the string

"=text (G14, "mm/dd/yy"

which made the cell information text.Then I did a copy on the column, then
"paste special-values" sequence followed by reformatting the cells as
dates.
That was the best way I could figure to eliminate the time portion of the
stamp.

I'm sure I've successfully excludedthis before without going through all
of
that, but I'm just trying to eliminate possibilites at this point.

"Reitanos" wrote:

Hmmm, it should work as you describe; I do this all the time and have
no problem.
Are your dates all stored as actual dates and not text?
I've just double-checked and it absolutely excludes 2/18/2008 when I
use "Does not equal 2/18" - note that the year is implied as the
current year and it will not hide 2/18/2007.
Please clarify.

On Jul 31, 1:15 pm, Ian wrote:
I'm working with data that has about 19,000 rows. The information is
date
specific, and there are scores of rows for each date..
I was working with February data recently and was trying to use the
autofilter to exclude February 18 (President's Day) as in "Does not
equal"
2/18/2008.

Much to my surprise it doesn't appear to work that way. It will exclude
one
row of February data, but not all. I can use the "is less than" and "is
greater than" path, but it seems like a long way around. I thought I
had used
the autofilter before to exclude all the rows of a specific date, but
apparently not. Any ideas out there?

Using Autofilter to exclude dates
 

The "does not equal" condition will exclude more than one row at a time.
--
David Biddulph

"Ian" wrote in message
...
It appears that how the data is formatted is not the issue. I did get the
cells to reflect only the date (although I took the long way around) but I
still can't exclude the date in queston. It is helpful to know that you
were
able to do it becaue I'm sure I've done it before on past data sets. This
one
has me stumped.

Incidentally, when you excluded the date, did you have multiple rows that
had that particular date and it exluded all of them? It does appear to
exclude one row of the date, but that's not really helpful.


"Reitanos" wrote:

Is there a reason that you're not just formatting it to LOOK
different?
Another way to get rid of the time is to use the TRUNC function to lop
it off.


On Jul 31, 3:20 pm, Ian wrote:
I tried that but it set the date to 1/0/1900.

"David Biddulph" wrote:
Wouldn't =MOD(G14,1) have done the job, if all you wanted to do was
get rid
of the time portion?
--
David Biddulph

"Ian" wrote in message
...
The column in questions references another column that is a
date/time
stamp.
When I first noticed problem I thought that might be the issue.

So the workflow I employed was: first I entered the string

"=text (G14, "mm/dd/yy"

which made the cell information text.Then I did a copy on the
column, then
"paste special-values" sequence followed by reformatting the cells
as
dates.
That was the best way I could figure to eliminate the time portion
of the
stamp.

I'm sure I've successfully excludedthis before without going
through all
of
that, but I'm just trying to eliminate possibilites at this point.

"Reitanos" wrote:

Hmmm, it should work as you describe; I do this all the time and
have
no problem.
Are your dates all stored as actual dates and not text?
I've just double-checked and it absolutely excludes 2/18/2008 when
I
use "Does not equal 2/18" - note that the year is implied as the
current year and it will not hide 2/18/2007.
Please clarify.

On Jul 31, 1:15 pm, Ian wrote:
I'm working with data that has about 19,000 rows. The
information is
date
specific, and there are scores of rows for each date..
I was working with February data recently and was trying to use
the
autofilter to exclude February 18 (President's Day) as in "Does
not
equal"
2/18/2008.

Much to my surprise it doesn't appear to work that way. It will
exclude
one
row of February data, but not all. I can use the "is less than"
and "is
greater than" path, but it seems like a long way around. I
thought I
had used
the autofilter before to exclude all the rows of a specific
date, but
apparently not. Any ideas out there?

Using Autofilter to exclude dates
 

Yes, it should. But for some reason it is not doing it. But at least I know
others are getting to work.

"David Biddulph" wrote:

The "does not equal" condition will exclude more than one row at a time.
--
David Biddulph

"Ian" wrote in message
...
It appears that how the data is formatted is not the issue. I did get the
cells to reflect only the date (although I took the long way around) but I
still can't exclude the date in queston. It is helpful to know that you
were
able to do it becaue I'm sure I've done it before on past data sets. This
one
has me stumped.

Incidentally, when you excluded the date, did you have multiple rows that
had that particular date and it exluded all of them? It does appear to
exclude one row of the date, but that's not really helpful.


"Reitanos" wrote:

Is there a reason that you're not just formatting it to LOOK
different?
Another way to get rid of the time is to use the TRUNC function to lop
it off.


On Jul 31, 3:20 pm, Ian wrote:
I tried that but it set the date to 1/0/1900.

"David Biddulph" wrote:
Wouldn't =MOD(G14,1) have done the job, if all you wanted to do was
get rid
of the time portion?
--
David Biddulph

"Ian" wrote in message
...
The column in questions references another column that is a
date/time
stamp.
When I first noticed problem I thought that might be the issue.

So the workflow I employed was: first I entered the string

"=text (G14, "mm/dd/yy"

which made the cell information text.Then I did a copy on the
column, then
"paste special-values" sequence followed by reformatting the cells
as
dates.
That was the best way I could figure to eliminate the time portion
of the
stamp.

I'm sure I've successfully excludedthis before without going
through all
of
that, but I'm just trying to eliminate possibilites at this point.

"Reitanos" wrote:

Hmmm, it should work as you describe; I do this all the time and
have
no problem.
Are your dates all stored as actual dates and not text?
I've just double-checked and it absolutely excludes 2/18/2008 when
I
use "Does not equal 2/18" - note that the year is implied as the
current year and it will not hide 2/18/2007.
Please clarify.

On Jul 31, 1:15 pm, Ian wrote:
I'm working with data that has about 19,000 rows. The
information is
date
specific, and there are scores of rows for each date..
I was working with February data recently and was trying to use
the
autofilter to exclude February 18 (President's Day) as in "Does
not
equal"
2/18/2008.

Much to my surprise it doesn't appear to work that way. It will
exclude
one
row of February data, but not all. I can use the "is less than"
and "is
greater than" path, but it seems like a long way around. I
thought I
had used
the autofilter before to exclude all the rows of a specific
date, but
apparently not. Any ideas out there?

Using Autofilter to exclude dates
 

Have you tried a helper column saying =IF(A2<DATE(2008,2,18)) to see which
rows are identified?
Or =IF(A2<--"2/18/2008") ?
Or =IF(A2<--"2/18") ?
--
David Biddulph

"Ian" wrote in message
...
Yes, it should. But for some reason it is not doing it. But at least I
know
others are getting to work.

"David Biddulph" wrote:

The "does not equal" condition will exclude more than one row at a time.
--
David Biddulph

"Ian" wrote in message
...
It appears that how the data is formatted is not the issue. I did get
the
cells to reflect only the date (although I took the long way around)
but I
still can't exclude the date in queston. It is helpful to know that you
were
able to do it becaue I'm sure I've done it before on past data sets.
This
one
has me stumped.

Incidentally, when you excluded the date, did you have multiple rows
that
had that particular date and it exluded all of them? It does appear to
exclude one row of the date, but that's not really helpful.


"Reitanos" wrote:

Is there a reason that you're not just formatting it to LOOK
different?
Another way to get rid of the time is to use the TRUNC function to lop
it off.


On Jul 31, 3:20 pm, Ian wrote:
I tried that but it set the date to 1/0/1900.

"David Biddulph" wrote:
Wouldn't =MOD(G14,1) have done the job, if all you wanted to do
was
get rid
of the time portion?
--
David Biddulph

"Ian" wrote in message
...
The column in questions references another column that is a
date/time
stamp.
When I first noticed problem I thought that might be the issue.

So the workflow I employed was: first I entered the string

"=text (G14, "mm/dd/yy"

which made the cell information text.Then I did a copy on the
column, then
"paste special-values" sequence followed by reformatting the
cells
as
dates.
That was the best way I could figure to eliminate the time
portion
of the
stamp.

I'm sure I've successfully excludedthis before without going
through all
of
that, but I'm just trying to eliminate possibilites at this
point.

"Reitanos" wrote:

Hmmm, it should work as you describe; I do this all the time
and
have
no problem.
Are your dates all stored as actual dates and not text?
I've just double-checked and it absolutely excludes 2/18/2008
when
I
use "Does not equal 2/18" - note that the year is implied as
the
current year and it will not hide 2/18/2007.
Please clarify.

On Jul 31, 1:15 pm, Ian wrote:
I'm working with data that has about 19,000 rows. The
information is
date
specific, and there are scores of rows for each date..
I was working with February data recently and was trying to
use
the
autofilter to exclude February 18 (President's Day) as in
"Does
not
equal"
2/18/2008.

Much to my surprise it doesn't appear to work that way. It
will
exclude
one
row of February data, but not all. I can use the "is less
than"
and "is
greater than" path, but it seems like a long way around. I
thought I
had used
the autofilter before to exclude all the rows of a specific
date, but
apparently not. Any ideas out there?

Using Autofilter to exclude dates
 

Of course, I didn't want those IFs.
Just =(A2<DATE(2008,2,18)) and so on.
--
David Biddulph

"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
Have you tried a helper column saying =IF(A2<DATE(2008,2,18)) to see
which rows are identified?
Or =IF(A2<--"2/18/2008") ?
Or =IF(A2<--"2/18") ?
--
David Biddulph

"Ian" wrote in message
...
Yes, it should. But for some reason it is not doing it. But at least I
know
others are getting to work.

"David Biddulph" wrote:

The "does not equal" condition will exclude more than one row at a time.
--
David Biddulph

"Ian" wrote in message
...
It appears that how the data is formatted is not the issue. I did get
the
cells to reflect only the date (although I took the long way around)
but I
still can't exclude the date in queston. It is helpful to know that
you
were
able to do it becaue I'm sure I've done it before on past data sets.
This
one
has me stumped.

Incidentally, when you excluded the date, did you have multiple rows
that
had that particular date and it exluded all of them? It does appear to
exclude one row of the date, but that's not really helpful.


"Reitanos" wrote:

Is there a reason that you're not just formatting it to LOOK
different?
Another way to get rid of the time is to use the TRUNC function to
lop
it off.


On Jul 31, 3:20 pm, Ian wrote:
I tried that but it set the date to 1/0/1900.

"David Biddulph" wrote:
Wouldn't =MOD(G14,1) have done the job, if all you wanted to do
was
get rid
of the time portion?
--
David Biddulph

"Ian" wrote in message
...
The column in questions references another column that is a
date/time
stamp.
When I first noticed problem I thought that might be the issue.

So the workflow I employed was: first I entered the string

"=text (G14, "mm/dd/yy"

which made the cell information text.Then I did a copy on the
column, then
"paste special-values" sequence followed by reformatting the
cells
as
dates.
That was the best way I could figure to eliminate the time
portion
of the
stamp.

I'm sure I've successfully excludedthis before without going
through all
of
that, but I'm just trying to eliminate possibilites at this
point.

"Reitanos" wrote:

Hmmm, it should work as you describe; I do this all the time
and
have
no problem.
Are your dates all stored as actual dates and not text?
I've just double-checked and it absolutely excludes 2/18/2008
when
I
use "Does not equal 2/18" - note that the year is implied as
the
current year and it will not hide 2/18/2007.
Please clarify.

On Jul 31, 1:15 pm, Ian
wrote:
I'm working with data that has about 19,000 rows. The
information is
date
specific, and there are scores of rows for each date..
I was working with February data recently and was trying to
use
the
autofilter to exclude February 18 (President's Day) as in
"Does
not
equal"
2/18/2008.

Much to my surprise it doesn't appear to work that way. It
will
exclude
one
row of February data, but not all. I can use the "is less
than"
and "is
greater than" path, but it seems like a long way around. I
thought I
had used
the autofilter before to exclude all the rows of a specific
date, but
apparently not. Any ideas out there?