Formula to get date from text string

  • Thread starter Thread starter Sara
  • Start date Start date
S

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
 
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
 
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
 
Back
Top