Using Autofilter to exclude dates

[Ian]

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?

 

[Reitanos]

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.

 

[Ian]

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.

 

[David Biddulph]

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

 

[Ian]

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

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

 

[Reitanos]

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.

 

[Ian]

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.

 

[David Biddulph]

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

 

[Ian]

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

 

[David Biddulph]

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]

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