Autofilter by date range yields only blanks

sheetspread

Well-known Member
Joined
Sep 19, 2005
Messages
5,151
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.
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Datsmart

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
How are you "Custom" filtering your date range?
Are your dates true excel dates or text?
 

sheetspread

Well-known Member
Joined
Sep 19, 2005
Messages
5,151
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

Well-known Member
Joined
Sep 19, 2005
Messages
5,151
..........

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

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
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

Well-known Member
Joined
Sep 19, 2005
Messages
5,151
............

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

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
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

Well-known Member
Joined
Sep 19, 2005
Messages
5,151
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

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
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.
 

Forum statistics

Threads
1,181,399
Messages
5,929,743
Members
436,687
Latest member
Glass of Gin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top