auto filter custom doesn't work right

[Karen]

using auto filter, custom, I am trying to display dates for a certain range
ex. greater than 1/1/06 and less than 1/31/06 but no results are displayed I
tried but greater than and less than and tried using both and/or but no
reaults I have another workbook that I used before that this work just fine
but won't with this workbook

 

[Max]

Perhaps the "dates" are not real dates recognized by Excel. Try this: Select
the col of "dates". Then click Data > Text to Columns > Next > Next. In step
3 of the wizard, under Col Data Format, check "Date", then select: MDY from
the droplist. Click Finish. That should convert all the dates to real dates.
Then try the filter again. And as a precaution that all data in the dates col
are selected before filtering, just select the entire col of dates, then only
click Data > Filter > Autofilter > Custom ... (Any blank rows/cells
in-between the data would "break" the autofilter)

 

[Karen]

I enter all of my dates again using 01/01/2006 instead of 01/01/06 and it
finally worked I'm sure it had something to do with it not recognizing them
as real dates as well and I probably did it the long way but it worked out
thank you for your help Karen

 

[Max]

Glad you got it sorted out. It's always better to avoid ambiguity by
inputting the full date.

 

[Nick Hodge]

Karen

Glad it worked out, as a date is just a number (1 is 1st Jan 1900),
formatted as a date you could have typed a 1 in a spare cell, copied that
and then highlight your 'dates' and the edit>paste special...>values and
multiply

This will force XL to see them as 'numbers' rather than text and multiplying
a number by 1 leaves the number. All you then need do is format the
'numbers' as dates

For explanations of dates and times in XL look here

http://www.cpearson.com/excel/datetime.htm

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England

(e-mail address removed)
www.nickhodge.co.uk