MrExcel Publishing
Your One Stop for Excel Tips & Solutions

xcell and (text)date field


Posted by kim on December 18, 2001 1:03 PM

i have an xcell document with a date field that is in text format
the field is in this (manually inputted) format yyyy-mm-dd
what i was wondering is ... i need the month and the date to come before the year so that i can send this to hr for anniversary announcements (ex mm-dd-yyyy)

is there anyway to convert this text number to a date format ... when i highlight the row and then click format/date nothing happens

or

if there is a way to trim the first 5 digits that will work also

thanks in advance


Posted by Mark W. on December 18, 2001 1:10 PM

Use the Data | Text to Columns... menu command.

Posted by Russell Hauf on December 18, 2001 1:12 PM

Try the DATEVALUE formula.

In a column next to your date (it doesn't actually have to be next to), type the formula:

=DATEVALUE(A1)

where A1 is where your date is. Then just format the cell however you want it (date format, that is).

Hope this helps,

Russell

Posted by kim on December 18, 2001 1:30 PM


<strong> i fixed it (kinda) what i did was a find/replace with the - as the find and replaced it with a /

the date works except it is sorting the dates by the year and then the month ... what i need is to sort by the month then the date

any suggestions?

thanks in advance!!!


Posted by Mark W. on December 18, 2001 1:40 PM

Find/Replace didn't fix your problem...

...you are still contending with text values. That's
why your formatting won't "take". Select the
cells containing the text representations of
dates, choose the Data | Text to Columns... menu
command, press [ Finish ], and format the resulting
date values as you wish.


Posted by Russell Hauf on December 18, 2001 4:09 PM

Use DATEVALUE!! :)

No need to make this harder than it is...(see my last message if necessary)