A minor simplification. Change this statement...

Case 4 To 20, 24 To 30

to this...

Case Else

Also, the OP may want to filter the event for certain cells only...

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Target, Range("C4:H9")) Is Nothing Then Exit Sub
With Target
If IsDate(.Value) Then
Select Case Day(.Value)
Case 1, 21, 31
.NumberFormat = "dddd d""st"" mmm, yyyy"
Case 2, 22
.NumberFormat = "dddd d""nd"" mmm, yyyy"
Case 3, 23
.NumberFormat = "dddd d""rd"" mmm, yyyy"
Case Else
.NumberFormat = "dddd d""th"" mmm, yyyy"
End Select
End If
End With
End Sub

Note to the OP... my example used the range C4:H9 to apply this event code
to; this was accomplished via the Range property call in the first line of
the code... change that range to suit your needs.

--
Rick (MVP - Excel)


"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
You could do it with event code behind the sheet.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If IsDate(.Value) Then
Select Case Day(.Value)
Case 1, 21, 31
.NumberFormat = "dddd d""st"" mmm, yyyy"
Case 2, 22
.NumberFormat = "dddd d""nd"" mmm, yyyy"
Case 3, 23
.NumberFormat = "dddd d""rd"" mmm, yyyy"
Case 4 To 20, 24 To 30
.NumberFormat = "dddd d""th"" mmm, yyyy"
End Select
End If
End With
End Sub


Gord Dibben MS Excel MVP

On Sat, 20 Dec 2008 14:58:38 +0000, Terry Pinnell
wrote:

Is there any way to specify that a date entered as say 20/12/08 should
be displayed in Excel 2000 as 'Sat 20 December 2008' please? Even
better, 'Saturday 20th December, 2008'?