text date to date format

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have column filled with:
'Apr 3 2005 4:37PM

And I need to change it to:
04/03/05 4:37PM

How do I get a textual date column into a sortable date/time column? I'm
having problems because it sorts everything by alpha then numeric. For
example, I have "Apr 15, 2005" before "Aug 10, 2003". I need to get these
sorted by year, month, day...not alphabetically. Thanks in advance for your
help!
 
you are right: the filter is not a time but a alpha sort

you may change the cell format or copy the value in an other cell and
put the new format in the new column. Like YYYY-MM-DD : so the alpha
sorting will be the same than the calendar sorting !
 
Hi. Do you know how I can grab the textual date and convert it to YYYY-MM-DD
format in a new column?
Thanks again.
 
Kyli,

The apostrophe (') at the beginning signals Excel that it's text, not a
date, thus it's being sorted alphabetically, not chronologically by date.
Also, there's no comma after Apr 3, which Excel needs to recognize it's a
date. And there needs to be a space between 4:37 and PM. Are they all like
this? How many are there?
 
Maybe you can try this (Edit|Undo if it doesn't work).

Select your range

Edit|Replace
what: (space)200
with: ,(space)200
replace all

If you have dates in the 1900's you'll have to do the equivalent for them.

Then one more time (maybe twice):

Edit|replace
what: PM
with: (space)PM
replace all

And the same for AM.

When I did this, excel saw the cells as dates/times and I could format them the
way I wanted--but the sorts were by dates.
 
Hi. Do you know how I can grab the textual date and convert it to YYYY-MM-DD
format in a new column?
Click to expand...

The DATE function will try to convert any entry to a date format
(maybe string to date) Then just 1- choose a date format YYYY-MM-DD in
the cell
OR 2- convert it again to a new string in the new format with the
function TEXT( date ; "YYYY-MM-DD")
 
Back
Top