• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 432
  • Last Modified:

Dates Won't Filter in Excel

I have a spreadsheet, which I have imported data into. There are several date columns in it, coming from two different sources. On one type, the dates are right-aligned in the column, and the Custom Filters work fine (say, Date >= 03/01/2011). The columns from the other source, however, have left-aligned dates, and the Custom Filters do not work at all. The regular filter does work. I have tried formatting the columns so that they are the same as the ones that work, (Date - Type *03/14/2001), but they still stay left-aligned, and do not filter. Any suggestions?
0
4charity
Asked:
4charity
  • 2
  • 2
  • 2
  • +1
2 Solutions
 
Rory ArchibaldCommented:
Your dates are actually text. Select a blank cell, copy it, then select your date cells and Edit-Paste Special and select Values.
0
 
jimyXCommented:
Most likely they are treated as text columns, so what you can do is convert the columns by using "Text to Columns".

If not working, could you attach sample of that in an Excel file please?
0
 
4charityAuthor Commented:
Woah.... I'm not sure I get that. If I copy a blank cell, and Paste Special Value INTO MY Date Cells?? I lose my dates.
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Rory ArchibaldCommented:
Sorry - forgot a key bit - you need to select Values and Add in the Paste Special dialog!
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Or you could select the whole column with the misbehaving "dates", then click Data > Text to columns > Next > Next > select "Date" and use the drop down list to specify the date format, i.e. DMY for Day-month-year or MDY for Month-day-year. Then click the "Finish" button.

cheers, teylyn
0
 
4charityAuthor Commented:
Text to Columns - that was the key!
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
too late for my text to columns suggestion, I guess.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now