How do I sort dates that are not formatted correctly?

C

curious1

8/8/2006
8/11/2006
8/1/2006

I cannot get the above dates to sort correctly in descending order when
imported from another source.
 
N

Niek Otten

Here's a checklist which will help you solve most known cases. Make a copy of your workbook before trying! Always use Excel's
ISNUMBER() function to check your cells; maybe you solved your problem in the first step!

· Format an empty cell as Number. Enter the number 1 in it. Edit>Copy.
Select your "numbers". Edit>Paste Special, check Multiply. Hopefully your cells are "real" Numbers now
· If that doesn't help, there may be spaces in your "numbers". You can use the LEN() function to compare the number
of characters that Excel sees in the cell with the number of characters you see. If you suspect spaces, use Excel's TRIM()
function to remove them
· If that doesn't help, there may be nonprintable characters in your "numbers". You can use Excel's CLEAN() function
to remove most of them
· If that doesn't help, there may be non-breaking spaces in your "numbers" (mostly acquired from Web Pages). Use
David McRitchie's TRIMALL() function to remove them. It can be downloaded here:
http://www.mvps.org/dmcritchie/excel/join.htm#trimall


--
Kind regards,

Niek Otten
Microsoft MVP - Excel


| 8/8/2006
| 8/11/2006
| 8/1/2006
|
| I cannot get the above dates to sort correctly in descending order when
| imported from another source.
 
C

curious1

Tried all your suggestions, nothing worked. I'm not savvy enough with the
TRIMALL info you provided.

Thank you!
 
D

Dave Peterson

What dates are these?

Are they dates in August (Aug 8, Aug 11, Aug 1) or is it Aug 8, Nov 8, and Jan
1?

Before you try this, make sure that the date order (mdy or dmy or whatever)
matches the windows regional settings for short dates (under the control panel).

Then select your range to fix and
edit|Replace
what: / (slash)
with: /
replace all

If you change that windows regional date setting, then change it back when
you're done and format the cells the way you want.
 
G

Gord Dibben

I copied your example into Excel and used Data>Text to Columns>Next>Next>Column
Data Format>Date>DMY or MDY>Finish and got valid dates to appear.

Your example dates are ambiguous so you make the choice of MDY or DMY


Gord Dibben MS Excel MVP
 
C

curious1

Dave, the dates are Aug 8, Aug 11, Aug 1.

I've discovered that the source I brought the data in from starts the dates
with a space for some reason. I've tried the formatting suggestions and set
my regional short date, if I manually type in the dates they sort OK;
however, not with my imported dates.

Any suggestions on how to eliminate the first 'space' before the dates and
then to have the column auto-format to my desired mm/dd/yyyy format?
Thanks for all your assistance.
 
D

Dave Peterson

How about eliminating all the spaces in that range?

Select the range first--don't include cells that should not be fixed.
Edit|replace
what: (spacebar)
with: (leave blank)
replace all

And if your windows regional settings for date format is mdy, then those cells
will be converted to real dates, too.
 
L

Lori

Use Gord's Text to columns approach with Fixed width selected in the
first step to remove leading/trailing spaces. Or if the data is only
dates you could choose Edit>Replace with a space entered in the find
box and nothing in the replace box.
 
Top