text to date value in formula

[Joe C]

I have a file I get where dates come in as text like

'9/1/2011

I want to convert that to

40787 which is the value of the actual date if it were texted to column to date.

Text(b1) = 40787 if I text to text to column

Is there some way to in the formula get the same result without texting to coulmn
if B1 = '9/1/2011?

Many thanks

 

[SteveO59L]

=right(l30,len(l30))*1

 

[VoG]

Try

=DATEVALUE(RIGHT(B1,LEN(B1)-1))

 

[Andrew Poulsom]

For me:

=A1+0

did the trick. Excel ignores the single quote, which is a hangover from Lotus 123 and signifies that the entry is text.

 

[Joe C]

=DATEVALUE(RIGHT(A24,LEN(A24)))

Actually worked but I am going to try AP's suggestion because I love adding nothing to something and changing it

 

[Joe C]

Both worked many thanks to all