How do I sort dates that are not formatted correctly?
 

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.

How do I sort dates that are not formatted correctly?
 

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. EditCopy.
Select your "numbers". EditPaste 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 he
http://www.mvps.org/dmcritchie/excel/join.htm#trimall


--
Kind regards,

Niek Otten
Microsoft MVP - Excel


"curious1" wrote in message ...
| 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.

How do I sort dates that are not formatted correctly?
 

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

Thank you!

"Niek Otten" wrote:

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. EditCopy.
Select your "numbers". EditPaste 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 he
http://www.mvps.org/dmcritchie/excel/join.htm#trimall


--
Kind regards,

Niek Otten
Microsoft MVP - Excel


"curious1" wrote in message ...
| 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.

How do I sort dates that are not formatted correctly?
 

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.

curious1 wrote:

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.

--

Dave Peterson

How do I sort dates that are not formatted correctly?
 

I copied your example into Excel and used DataText to ColumnsNextNextColumn
Data FormatDateDMY or MDYFinish 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

On Wed, 1 Nov 2006 07:11:03 -0800, curious1
wrote:

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.

How do I sort dates that are not formatted correctly?
 

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.

"Dave Peterson" wrote:

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.

curious1 wrote:

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.

--

Dave Peterson

How do I sort dates that are not formatted correctly?
 

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.

curious1 wrote:

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.

"Dave Peterson" wrote:

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.

curious1 wrote:

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.

--

Dave Peterson


--

Dave Peterson

How do I sort dates that are not formatted correctly?
 

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 EditReplace with a space entered in the find
box and nothing in the replace box.

curious1 wrote:
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.

"Dave Peterson" wrote:

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.

curious1 wrote:

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.

--

Dave Peterson