Autofilter by Date in Excel 2002
 

I am having a problem filtering using the custom filter in Autofilter. I
want to custom filter by dates greater than - for instance 1/01/1994. I
only get the 1900 dates and not the 2000 dates.

At first this worksheet had dates in 2-digit year format, and not all of
them were recognized as numbers - some were seen as text. I inserted a
column and manually re-entered dates using a 4-digit year and the problem
continues. I've multiplied by the number 1. I've copy/paste
special\values. I've copy/paste special\values the entire worksheet to a
new workbook. Is there something inherently wrong/different about filtering
dates that I've not yet stumbled across?

Try double checking your dates.

if your data was in A2:A101, put this in an empty cell:
=counta(a2:a101)
and this in another cell
=count(a2:a101)

If these numbers don't match, then not all your entries are dates.

Depending on what your text dates look like, it could be as easy as selecting
that range and changing / to / (replace all) (12/31/1999 will be reevaluated as
a date).

Brenda Rueter wrote:

I am having a problem filtering using the custom filter in Autofilter. I
want to custom filter by dates greater than - for instance 1/01/1994. I
only get the 1900 dates and not the 2000 dates.

At first this worksheet had dates in 2-digit year format, and not all of
them were recognized as numbers - some were seen as text. I inserted a
column and manually re-entered dates using a 4-digit year and the problem
continues. I've multiplied by the number 1. I've copy/paste
special\values. I've copy/paste special\values the entire worksheet to a
new workbook. Is there something inherently wrong/different about filtering
dates that I've not yet stumbled across?

--

Dave Peterson