Changing Text to a Date

[michael_allen_24]

Hey everyone! Have a quick question for you: I have this text in a cell:<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
<TABLE style="WIDTH: 128pt; BORDER-COLLAPSE: collapse; mso-yfti-tbllook: 1184; mso-padding-alt: 0in 0in 0in 0in" class=MsoNormalTable border=0 cellSpacing=0 cellPadding=0 width=171><TBODY><TR style="HEIGHT: 15pt; mso-yfti-irow: 0; mso-yfti-firstrow: yes; mso-yfti-lastrow: yes"><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; WIDTH: 128pt; PADDING-RIGHT: 0in; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8; PADDING-TOP: 0in" width=171>Jul11<o></o>
</TD></TR></TBODY></TABLE>
<o></o>
I want it to display 7/11/2011.<o></o>
<o></o>
Formatting the cell to display the date does not work, probably because it is text? Not sure.<o></o>
<o></o>
What is the easiest formula to automatically do this for me? <o></o>
<o> </o>
Thanks in advance.<o></o>

 

[jim may]

Excel Workbook
	A	B	C
1	Jul11	40735	<< Now Format as Date
2
Sheet1

Excel 2007

Cell Formulas
Range	Formula
B1	=DATEVALUE(A1)

 

[Andrew Poulsom]

You can use:

=A1+0

or to convert it in place choose Data|Text To Columns and click Finish.

 

[michael_allen_24]

Perfect. Thanks!

 

[HalfAce]

Or...
(Jul11 being in A1)
=(--A1) and format the formula cell as a date.

 

[Andrew Poulsom]

Excel Workbook
	A	B	C
1	Jul11	40735	<< Now Format as Date
2
Sheet1

Excel 2007

Cell Formulas
Range	Formula
B1	=DATEVALUE(A1)

In the UK I get 40725, 01 July 2011. Same with my formula.

 

[HalfAce]

So... in the UK, Jul11 is being viewed as 'July 2011' and here in the states it's seen as July 11th (of the current year.)

Hmmm... good to know.

 

[Andrew Poulsom]

So... in the UK, Jul11 is being viewed as 'July 2011' and here in the states it's seen as July 11th (of the current year.)

Hmmm... good to know.

I think it's a **** nuisance.

 

[michael_allen_24]

One more quick question:<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
This is my original text in cell A1: <o></o>
<o></o>
2/Jul11<o></o>
<o></o>
I am using these formulas to pull apart the text:<o></o>
<o></o>
Cell B1 =FIND("/",A2)<o></o>
Cell C1 =LEFT(A2,B2-1)<o></o>
<o></o>
The result is as it should be:<o></o>
Cell C1 = 2<o></o>
<o></o>
But the format that comes back does not recognize it as 2. I tried the Trim function but that did not help. Any ideas?<o></o>

 

[danzon]

=LEFT(A2,B2-1)*1<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>