convert text into date

[paynod]

I'm working on a spreadsheet where I have the text 'Saturday 4 October 2014'. I want to convert this into an actual date.
Can anyone help?

Regards,
Tim

[boopathiraja]

Try with =DATEVALUE() else post a sample in excel

[Speshul]

If the dates you need converted are all in the same format you can use this

=RIGHT(A1,LEN(A1)-FIND(" ",A1))

It will take all text after the first occurance of SPACE (right after the "day")

So 'Saturday 4 October 2014'
becomes
'4 October 2014'

Which excel can read as a date

You can further convert it into a date format using
=TEXT(RIGHT(A1,LEN(A1)-FIND(" ",A1)),"mm/dd/yyyy")

[newdoverman]

I came up with this, which may have redundant parts in order to try and eliminate any oddities that may occur. This will result in a date serial number.

Please Login or Register  to view this content.

Format in the date format of your choice.

[Speshul]

I didn't think of it before but assuming the ' marks are included in the cell you can use either of these as well.

=LEFT(RIGHT(A1,LEN(A1)-FIND(" ",A1)),LEN(RIGHT(A1,LEN(A1)-FIND(" ",A1)))-1)
Or
=TEXT(LEFT(RIGHT(A1,LEN(A1)-FIND(" ",A1)),LEN(RIGHT(A1,LEN(A1)-FIND(" ",A1)))-1),"mm/dd/yyyy")


newdoverman's formula is probably more robust if there are variances in the data. My formula assumes there will always be:

'WEEKDAY[SPACE]DAY#[SPACE]MONTH[SPACE]YEAR]'