convert string to date

[Melounjan]

Is there a function I can use to convert string value to
dat? For example March 28 2004 to 03/28/2004
Thanks..

 

[Frank Kabel]

Hi
try
=DATEVALUE(MID(A1,FIND(" ",A1)+1,2) & "-" & LEFT(A1,FIND(" ",A1)-1) &
"-" & RIGHT(A1,4))
and format this cell as date

 

[Bernie Deitrick]

Melounjan,

If you want a programming solution:

Sub TryNow()
Dim DateString As String
Dim myDate As Date

DateString = "March 28 2004"
myDate = CDate(DateString)
MsgBox Format(myDate, "mm/dd/yyyy")

'Or, it can all be done in one line
MsgBox Format(CDate("March 28 2004"), "mm/dd/yyyy")
End Sub

HTH,
Bernie
MS Excel MVP

 

[Norman Harker]

Hi Frank!

Small simplification:

=DATEVALUE(MID(A9,FIND(" ",A9)+1,2) & "-" & LEFT(A9,3) &"-" &
RIGHT(A9,4))

DATEVALUE accepts dd-mmm-yyyy so we don't need to find the whole month
name, just the first three letters.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.