Autofilter Issue

[PWS]

Autofiltering Dates - stored as string

Is there a way to Autofilter a column of dates, when these dates are
stored as strings?.

15 Apr 2007
15 Apr 2007
15 Apr 2007
16 Apr 2007
16 Apr 2007
17 Apr 2007
17 Apr 2007
17 Apr 2007

When I set the Custom Autofiler, using 'does not equal' and then
selecting '16 Apr 2007' from the drop-down list, this has no effect.
When I return to the Custom Autofiler dialog I notice that the
criertia has been changed to 39188.

The format of the cells are set to text, but Excel will not leave the
criteria as a string it converts it to a number - can this be stopped?

If not how do I filter my range of text dates only showing thoese
which match a criteria which is a valid date as a string?

 

[Guest]

Pre-format cells as Text or enter ' before date.

 

[Guest]

Wow, that's weird. I can't get it to work either. If you select Equals 16
Apr 2007 it works. However, if you select Doesn't Equal 16 Apr 2007 it
doesn't work. Is it possible to change that field (column) to a numeric
field rather than text? That way everything would work normally.

 

[PWS]

I wish!

Does any mVP or anyone one from Microsoft have a solution?

 

[Peo Sjoblom]

Put 1 in an empty cell, format the cell as a date, copy the cell
select all text dates, do edit>paste special and select multiply