On Tue, 31 May 2005 23:01:52 GMT, "RTANSW via OfficeKB.com"
wrote:

Hi,

I was wondering if there was a date format that anyone was aware of that
would make the date appear as such:

Entered in as 30/12/1979
Displayed as 30th December, 1979

Thanks in Advance,

RTANSW

There is no specific format; you would have to change the format each time you
entered a date, to use the appropriate ordinal number.

You could use an event triggered macro. Let us say, for example, that your
date is in A1.

Right click the worksheet tab; select View Code, and paste the code below into
the window that opens.

Then enter a date, or a formula returning a date, into A1.

This could be made more efficient, depending on the details of your data entry.

=================================
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Suffix As String
Dim Dt As Range

Set Dt = Range("A1")

If Not IsNumeric(Dt) And Not IsDate(Dt) Then Exit Sub
'Dates returned from functions, like TODAY(), will fail IsDate

Select Case Day(Dt)
Case Is = 1, 21, 31
Suffix = "st"
Case Is = 2, 22
Suffix = "nd"
Case Is = 3, 23
Suffix = "rd"
Case Else
Suffix = "th"
End Select

Dt.NumberFormat = "d""" & Suffix & """ mmmm, yyyy"

End Sub
================================


--ron