Formula to get date from text string

[Sara]

This is what the cell currently looks like:

[10/01/09 11:30PM]

I would like the formula to return only: 10/01/09

Does anyone know what formula I should use? Any help would be greatly
appreciated.

Thanks!!

Sara

 

[Pete_UK]

Here's one way:

=--MID(A1,2,8)

though this will only work if the date is in the normal format for
your region (does it mean 10th January 2009, or 1st October 2009 ?).

A safer way might be:

=DATE(2000+MID(A1,8,2),MID(A1,5,2),MID(A1,2,2))

or:

=DATE(2000+MID(A1,8,2),MID(A1,2,2),MID(A1,5,2))

depending on the answer to my earlier question.

Hope this helps.

Pete

 

[Roger Govier]

Hi Sara

One way
=--INT((SUBSTITUTE(SUBSTITUTE(A11,"[",""),"PM]","")))

This will return the serial number of the date.
Format the cell in whatever date format you wish to see the result