date format with st nd rd

[Pierre Laporte]

I wish to format the date using the st nd rd th after the day as in
January 1st, 2003 or March 3rd, 1997
How can this be done ? Can i make my own formatting function ?

 

[Harlan Grove]

I wish to format the date using the st nd rd th after the day as in
January 1st, 2003 or March 3rd, 1997
How can this be done ? Can i make my own formatting function ?

This isn't supportde in any number formats, so your dates would have to be
string formulas, and they wouldn't be usable as date values without stripping
out the ordinal suffixes.

If that's OK for you, you could try

CHOOSE(MIN(4,MOD(DAY(SomeDate)+3*(ABS(MOD(DAY(SomeDate),100)-12)<=1)-1,10)+1),
"st","nd","rd","th")

to give the ordinal suffix for the date value SomeDate.

 

[Norman Harker]

Hi Pierre!

Using the ordinal formula found at:

Chip Pearson:
http://www.cpearson.com/excel/ordinal.htm

I can get what you want by formula applied to a date in A1:

=TEXT(A1,"mmmm")&"
"&DAY(A1)&IF(AND(DAY(A1)>=10,DAY(A1)<=14),"th",CHOOSE(MOD(DAY(A1),10)+
1,"th","st","nd","rd","th","th","th","th","th","th"))&",
"&TEXT(A1,"yyyy")

AFAIK you can't get it by formatting. You should note that the result
is text and not a date serial number.

You'll also find coverage of ordinal dates at:

Dave McRitchie:
http://www.mvps.org/dmcritchie/excel/datetime.htm#ordinal

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Holidays and Observances Wednesday 16th July: Argentina (Independence
Day); Brazil (Sao Paulo State Civil Holiday); Isle of Man (Senior Race
Day); Morocco (King Hassan II's Birthday); Palau (Constitution Day).
Celebration: Baha'i (Martyrdom of the Bab).
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.