Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
With Target
If .Column = 1 Then
If Len(.Value) - Len(Replace(.Value, "/", "")) = 1 Then
.NumberFormat = "mmmm, yyyy"
ElseIf Len(.Value) - Len(Replace(.Value, "/", "")) = 2 Then
.NumberFormat = "d mmmm, yyyy"
Else
.NumberFormat = "@"
End If
End If
End With

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.




--

HTH

RP
(remove nothere from the email address if mailing direct)


"Manta.ray" wrote in message
...
I have a specific need to show 'month and year' and 'day, month and year'
in
the same column. Because of multiple user input, I would like to make
this
as simple as possible. If they enter 1/05, I want to see January, 2005
and
if the enter 1/10/05, I want to see January 10, 2005. From what I can
see,
there is no way to format the entire column and acheive the stated goal.
One
must choose one format and customize/reformat individual cells, which
defeats
the purpose.

Any magic out there?