Unlike Tom I'm assuming that your cell's data source is string not a
formatted date.


- Rm

"Robert Mulroney" wrote:

As far as I know there is no Excel function that does exactly what you want
to do. I've written the following function that will convert your date format
to an excel recongnisable format for you.

Go to the VBA Editor -- [alt][F11] and copy and paste this code in to a
new module (Insert new module).

You can then use this new function on a worksheet by adding the formula

= Neals_Date("Jan 01 1967")


'______________________copy code _______________

Function Neals_Date(mydate As String) As Date

Dim m As Integer
Dim d As Integer
Dim y As Integer

m = MonthValue(Left(mydate, 3))

d = CInt(Mid(mydate, 5, 2))

y = CInt(Right(mydate, 4))

Neals_Date = DateSerial(y, m, d)


End Function


Function MonthValue(strThisMonth As String) As Integer

Select Case LCase(strThisMonth)
Case Is = "jul"
MonthValue = 1
Case Is = "aug"
MonthValue = 2
Case Is = "sep"
MonthValue = 3
Case Is = "oct"
MonthValue = 4
Case Is = "nov"
MonthValue = 5
Case Is = "dec"
MonthValue = 6
Case Is = "jan"
MonthValue = 7
Case Is = "feb"
MonthValue = 8
Case Is = "mar"
MonthValue = 9
Case Is = "apr"
MonthValue = 10
Case Is = "may"
MonthValue = 11
Case Is = "jun"
MonthValue = 12
Case Else
MonthValue = 0
End Select
End Function

'______________________End of code _______________



- Rm

"Neal" wrote:

I have a cell with the date formatted as Jan 01 1967 and I would like to
write an expression to change it to 01/01/1967 but I cant seem to find an
expression on line that coan help. Is there an expression to change the date
format?