Date Format

[Guest]

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?

 

[Tom Ogilvy]

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.

 

[Guest]

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

 

[Tom Ogilvy]

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

 

[Tom Ogilvy]

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 here:

? 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

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

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?

 

[Guest]

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


- Rm

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

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?