Formatting Dates

[Richard A. King]

Is there anyway in Excel, similar to the /*ordinal switch in Word, to format
dates as :- 28th May, 1999 ?

--
Regards, Richard
(remove '.stop' if replying by e-mail.)

[DMcRitchie]

Hi Richard,

If the st, nd, rd, th suffixes are not important simply format the date as
dd mmm, yyyy under format, custom; otherwise, a worksheet solution might be as
follows:

=DAY(A2)&IF(MOD(DAY(A2),10)=1, "st", IF(MOD(DAY(A2),10)=2,"nd",
IF(MOD(DAY(A2),10)=3, "rd","th"))) & " " & TEXT(A2,"mmm, yyyy")

Of course this might look simpler if you made a function to do the same thing.

HTH,
David McRitchie
My Excel Page: http://members.aol.com/dmcritchie/excel/excel.htm

[Ron Rosenfeld]

You could use a macro.

I think the following macro will format dates entered into the worksheet as
you stated in your message.

Enter under Tools/Macro/Visual Basic Editor. Then double-click the
worksheet (sheet one) to open a window and paste the following:

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

Richard A. King wrote in message ...

[DMcRitchie]

Hi,
I see from Ron Rosenfeld's reply that I misstated the worksheet formula as it
did not report 11th but 11st. A corrected worksheet formula appears below.

=DAY(A2)&IF(INT(MOD(DAY(A2),100)/10)=1, "th", IF(MOD(DAY(A2),10)=1, "st",
IF(MOD(DAY(A2),10)=2,"nd", IF(MOD(DAY(A2),10)=3, "rd","th"))))& " " &
TEXT(A2,"mmmm, yyyy")

or as a Function which I said would be neater to use and is based on Ron's
macro.

=OrdDate("11/12/1999")
=OrdDate(A2)

Function OrdDate(arg)
dd = Day(arg)
mmmm = Format(Month(arg), "mmmm")
yyyy = Year(arg)
Select Case Day(arg)
Case 1, 21, 31
OrdDate = dd & "st " & mmmm & ", " & yyyy
Case 2, 22
OrdDate = dd & "nd " & mmmm & ", " & yyyy
Case 3, 23
OrdDate = dd & "rd " & mmmm & ", " & yyyy

Case 4 To 20, 24 To 30

OrdDate = dd & "th " & mmmm & ", " & yyyy
End Select
End Function

Ron's macro (for XL97) has an advantage in not using a second cell to display
a text format. It is used as a Worksheet_Change and tests for date, so all
dates on that worksheet would be formatted with the very long date format when
changed or recalculated.

HTH,
David McRitchie

<<---previous postings --
From: "Ron Rosenfeld" <ronros...@earthlink.net>

>Regards, Richard
---end of previous postings>>

[Ron Rosenfeld]

David,

Two thoughts on your solution.

Your worksheet formula solution will give an error message if A2 cannot be
interpreted as a date. This may present a problem or an advantage.

Your macro solution results in data of the TEXT type being in the target
box, whereas mine results in retention of the DATE data type. Again, this
may be an advantage or disadvantage, depending on the circumstances.

Oh, you need to change:

mmmm = Format(Month(arg), "mmmm")

to
mmmm = Format(arg, "mmmm")
to make it work correctly.

Best,
ron

DMcRitchie wrote in message
<19990529005619...@ng-cr1.aol.com>...

[DMcRitchie]

Thanks Ron,
Guess I'm not doing too well on this as that was my second error on this one,
indeed that line within the FUNCTION should have been

mmmm = Format(arg, "mmmm")

David McRitchie
My Excel Pages: http://members.aol.com/dmcritchie/excel/datetime.htm
datetime page: http://members.aol.com/dmcritchie/excel/datetime.htm

Even though DejaNews appears to be broken this morning, this thread can
otherwise be located via one of it's postings:
http://www.deja.com/getdoc.xp?AN=483202809

>From: "Ron Rosenfeld" <ronros...@earthlink.net>
[most of posting clipped out]
>Your macro [actually a function] solution results in data of the TEXT type
being in the target
>box, whereas mine results in retention of the DATE data type. Again, this
>may be an advantage or disadvantage, depending on the circumstances.

I know, I guess your comment is more clear than mine was. The main two
problems I have with your macro is 1) it is XL97, and 2) the macro would need
to be run under a different name whenever values changed, if one didn't have
XL97. The problem with my function and worksheet solutions are that they
occupy (waste) another cell. That original cell can still be referred to
numerically for its serial date.