• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 374
  • 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

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