We help IT Professionals succeed at work.

Date format, downloaded list is not formatted correctly

pdvsa
pdvsa used Ask the Experts™
on
experts,

I have this dowloaded spreadsheet with a  column of dates.
The dates are not in a format that allows for proper sorting.

The format is like this:  18/APR/2012

i forced the format as as a date but still can not get it to sort properly.  

It will only sort the Days and not the month or year.  
meaning a list would be sorted ascending like this:

18/APR/2012
20/JAN/2012
21/DEC/2011

]Notice the days sort ascending but it should be sorted beginning with year 2011 but notice it is last and not first.  

Is there an easy way to modify the column to format it correctly to allow for proper sorting?

thank you
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Testing the sample list you provided, (MS Excel 2003 & 2007) by importing, pasting or direct input - the cells were automatically recognized as dates, the "/" converted to "-" and sorted properly.

You may need to check to see that the cells do not contain any leading (or trailing) characters, as they could override the format and be interpreted as text by Excel.
Most Valuable Expert 2011
Awarded 2010
Commented:
Hello,

these values are still text, not dates, hence they will not sort properly.

Select the column with the dates, then click Data > Text to columns > Delimited > next > next > select "Date" and in the drop down select DMY > Finish

That should make the text into real dates.

cheers, teylyn
If there is a leading space Excel will treat the cell as text regardless of the cell's format when the cell contains mixed alpha and numeric characters...
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
Information downloaded from the web often contains embedded non-breaking spaces, ASCII character 160. These look like spaces, but cannot be removed by TRIM function. If (as suspected by macwizard) your data contains leading or trailing spaces, you should remove them before trying to convert the dates into a useful form.

One way of removing ASCII 160 non-breaking spaces, then converting to a date is a formula like:
=--TRIM(SUBSTITUTE(A1,CHAR(160)," "))
The -- in front of TRIM does the date conversion. The SUBSTITUTE replaces non-breaking spaces with regular spaces. The TRIM gets rid of leading and trailing spaces.

The result of such a formula will be a number like 40668. This can be formatted as a date.
Most Valuable Expert 2011
Awarded 2010

Commented:
@macwizard, the text-to-columns approach will get rid of leading and trailing spaces and return the date.

If the text-to-columns does not work, then non-breaking spaces as in byundt's comment are probably the issue.
pdvsaProject finance

Author

Commented:
that worked perfectly.  Byundt, thank you for that tip.  I will write that one down.