converting Text to date

[ulicki]

Hi,

In cell A6 I have this AU9602 Dec 15, 1995

What you cannot see in is that between the "2" and the "D" is the character for carriage return char(13); it looks like a square.

Ok in cell B6 I have this formula to get the date out of A6: =MID(A6,FIND(CHAR(13),A6,1)+1,12)
So the value in B6 displays Dec 15, 1995 and is text and I want to change it to date. So I format the cell and choose Date format mm/dd/yy, but the cell does not change.

What do I do?

Thanks,
Mike

 

[SkipVought]

Hi,

Why do Dates and Times seem to be so much trouble? faq68-5827.

You may need to set up a list with the Month abbreviations. You can use the Match(LookupValue,MonthList,0) to return the month number.
[tt]
=DATE(year,month,day)
[/tt]
returns a REAL MS DATE.




Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]

 

[xlhelp]

or Try

=MID(A6,FIND(CHAR(13),A6,1)+1,12)*1

http://www.geniusmedia.ca

 

[GlennUK]

In C6 put =DATEVALUE(B6)

then copy C6, and select B6 and do Edit/Paste Special/Values.




Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[ulicki]

Thanks, they all worked