Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 7
Default 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.
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,440
Default 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.


  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 7
Default 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.



  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default 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
  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 22,906
Default 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.




  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 7
Default 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

  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default 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
  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 340
Default 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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
If a range of cells in Excel are formatted as dates ie. (mm/dd/yy) mfoley Excel Discussion (Misc queries) 3 January 20th 07 06:50 PM
CAN YOU SORT DATES IN A/Z OR Z/A MODE? IN EXCEL Help with Excel Excel Worksheet Functions 2 August 23rd 06 07:02 PM
dates automatically formatted wrong bigajosepi Excel Discussion (Misc queries) 1 August 22nd 06 04:56 AM
extract and sort dates cityfc Excel Discussion (Misc queries) 0 January 11th 06 09:06 PM
How do you sort a list of dates into date order Tegwen Excel Discussion (Misc queries) 2 August 31st 05 02:04 PM


All times are GMT +1. The time now is 06:42 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"