Date Formats Available

[RTANSW via OfficeKB.com]

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

--
Message posted via http://www.officekb.com

[JE McGimpsey]

You'd need a VBA event macro to do this.

If you can live with

30 December, 1979

then

Format/Cells/Number/Custom dd mmmm, yyyy

will do.

In article <[email protected]>,
"RTANSW via OfficeKB.com" <[email protected]> wrote:

> 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

[Ron Rosenfeld]

On Tue, 31 May 2005 23:01:52 GMT, "RTANSW via OfficeKB.com"
<[email protected]> 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

[RTANSW via OfficeKB.com]

Thankyou very much!

--
Message posted via http://www.officekb.com