auto filter custom doesn't work right

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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)
 
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
 
Glad you got it sorted out. It's always better to avoid ambiguity by
inputting the full date.
 
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
 
Back
Top