On Thu, 30 Apr 2009 13:05:04 -0700, OssieMac
wrote:

Hi Ossie,

Thanks for that example. I see how that would work.

I still don't know exactly what you want to do with the mmm yyyy format. Do
you simply want it to display like that on the worksheet?

Yes - here's what I ended up using, although now I see Haralds example
this is laughable!

--
Sub UpDateDate()
Dim j, y, c, m, D
D = "JanFebMarAprMayJunJulAugSepOctNovDec"
With Worksheets("Sheet1")
For j = 2002 To 4000
c = .Cells(j, "B") '00/00/1965'
If c "" Then
y = Right(c, 4)
m = Val(Mid(c, 4, 2))
Select Case m
Case 1 To 12
m = (m * 3) - 2
.Cells(j, "B") = Mid(D, m, 3) & " " & y
Case 0
.Cells(j, "B") = y
Case Else
Stop
End Select
End If
Next j
End With
End Sub
--

If various dates are in column A then Custom Format column B to "mmm yyyy"
Now if date is in cell A2 then in B2 insert:-
=DATEVALUE(TEXT(A2,"mmm yyyy"))

In VBA code it would be as follows:-

Sub test()
With Sheets("Sheet1")
.Columns(2).NumberFormat = "mmm yyyy"
.Cells(2, 2) = DateValue(Format(.Cells(2, 1), "mmm yyyy"))
End With
End Sub


One for the book. I'd not seen DateValue before.

Thanks - Kirk