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?

activecell.Numberformt = "mm/dd/yyyy"

activecell.Numberformat = "mmm dd, yyyy"

Make sure the date is actually stored as a date and not as a string/text
(which will not format)

assume it is A1
=isnumber(A1)
if that returns false, the it isn't stored as a date.

--
Regards,
Tom Ogilvy

"Neal" wrote in message
...
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?

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?

activecell.Numberformt = "mm/dd/yyyy"
should read
activecell.Numberformat = "mm/dd/yyyy"

--
Regards,
Tom Ogilvy

"Tom Ogilvy" wrote in message
...
activecell.Numberformt = "mm/dd/yyyy"

activecell.Numberformat = "mmm dd, yyyy"

Make sure the date is actually stored as a date and not as a string/text
(which will not format)

assume it is A1
=isnumber(A1)
if that returns false, the it isn't stored as a date.

--
Regards,
Tom Ogilvy

"Neal" wrote in message
...
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?

It depends on whether you believe a cell showing Jan 01 1967 is actually
stored as a date serial. It can be as demo'd from the immediate window:
? activecell.Text
Jan 01 1967
? activecell.Value2
24473
activecell.NumberFormat = "mm/dd/yyyy"
? activecell.Text
01/01/1967


also, VBA doesn't seem to have a problem seeing it as a date as demo'd he

? isdate("Jan 01 1967")
True
? datevalue("Jan 01 1967")
1/1/67

so event is it is stored as a string and a function is required, your
function could be reduced to

Function Neals_Date(mydate As String) As Date
if isdate(mydate) then
Neals_Date = cdate(mydate)
else
Neals_Date = cverr(xlErrValue)
end if
End Function


again, from the immediate window with the above in a module:

? Neals_Date("Jan 01 1967")
1/1/67
? format(Neals_Date("Jan 01 1967"),"mm/dd/yyyy")
01/01/1967

--
Regards,
Tom Ogilvy



"Robert Mulroney" '''' wrote in message
...
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?

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?