Autofilter by date range yields only blanks

[sheetspread]

Has anyone had this problem? I have numerous records ordered by date and when I use the custom feature on autofilter to show only a specific date range it doesn't show anything. It will show individual dates though if I ask it to. The date cells are properly formatted as such and I can think of no other possible reasons.

 

[Datsmart]

How are you "Custom" filtering your date range?
Are your dates true excel dates or text?

 

[sheetspread]

well.............

I formatted the cells to date, not text, but is anything else required? I don't recall seeing a setting in the autofilter that refers specifically to dates or date formatting. I noticed that when you sort dates, it does it in a weird order, almost exact but not.

 

[sheetspread]

..........

Additional Info:

I use the greater than 6/29/2007 (check "and" not "or") less than 8/1/2007. I get blanks. Not choosing custom but hitting a single date, say 1/13/2007 gives me whichever rows correspond to that value.

 

[Datsmart]

If your dates do not sort as expected, they are probably text instead of True Excel dates.
To test a cell to see if it has a proper serial date:
Select the date cell.
Hit Ctrl-Shift-~
You should get a 5 digit number.
Todays date of 8/2/07 will give you 39296.
Ctrl-Shift-# will convert it back to a Date, although it will be in the default date format.

 

[sheetspread]

............

Dint work, but thanks anyway. I've had this problem before with excel 2003, formatting as date (or sometimes text to number format) doesn't happen though it should. Once I had to format cell by cell and for some reason it worked, though this is obviously an inefficient solution. Maybe I have overlooked something, but it seems that the wonderful debuggers at MS may have also.

 

[Datsmart]

The Format of the cells is not important. What is important when comparing dates is that they both are truly dates. Excel see's dates as "Serial Numbers".
Excel sees August 1st, 2007 as 39295.
Excel sees 08/01/2007 as 39295.
If your data comes in as text, you can not compare it to a date without converting it to a true serial Date.

You didn't post any sample "Dates" to show what the database dates look like. Here is a suggestion to try and convert your Text Dates to true dates.

Suggestion 1. (convert by Text to Columns)
Select column of Text dates you want to convert.
Click Data | Text to Columns….
Click Next to get to 3rd page of Wizard
Under "Column data format" choose "Date" and then click dropdown arrow
Choose format of Text you are converting from. ie:"YMD" for Year Month Day or whatever the text looks like.
Click Finish

Suggestion 2. (convert by addition)
Select a blank cell
Hit Ctrl-C to copy
Select your number range
Right Click and choose Paste Special
Click Add, then OK.
Then Format the cells as date.

Suggestion 3. (If you have Excel 2003)
Select the cells affected and right click on the yellow "!" that shows up next to the cells and choose convert to number.
(Must have "Number stored as Text" checked in Tools|Options|Error Checking)

 

[sheetspread]

That worked

I just tried the text to columns method worked, though I still believe that when you type a date, like 7/11/2007, excel should know by now what that means. I use TTC alot and had noticed the date function in the past. Still not sure what the two have to do with each other (you could separate by "/"). Thanks for staying in touch John.

 

[Datsmart]

If you pre-format a cell as Text, then input 8/3, you will get 8/3 as Text.
If the cell is formated as General, then input 8/3, you will get a full Date in whatever default format style your PC uses. Mine will display 3-Aug. When you select the cell, 8/3/2007 shows in the formula bar.

Not sure how your cells got formated to Text, but that is probably what happened.