Date format, downloaded list is not formatted correctly
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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...
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
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.
One way of removing ASCII 160 non-breaking spaces, then converting to a date is a formula like:
=--TRIM(SUBSTITUTE(A1,CHAR
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.
@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.
If the text-to-columns does not work, then non-breaking spaces as in byundt's comment are probably the issue.
ASKER
that worked perfectly. Byundt, thank you for that tip. I will write that one down.
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.