Add st and th to dates
 

Hi All,

Is there a way in excel to format a date so that it automatically includes
the "th" and "st".

I.e. so 1/8/05 -- 1st August 2005?

Ta

Andi

Hello-

Excel provides no such date formatting, and trying to create a custom format
like that would be impossible due to the variable 'st', 'rd', 'th', etc. &
when each should be used.

Perhaps it can be accomplished with VBA, but unless someone has it available
& is willing to share, it would probably be more trouble to write than its
worth... Unless you need to use the dates for calculations.

Regards |:)

"Andibevan" wrote:

Hi All,

Is there a way in excel to format a date so that it automatically includes
the "th" and "st".

I.e. so 1/8/05 -- 1st August 2005?

Ta

Andi

Andi,

Chip Pearson can get you started...
http://www.cpearson.com/excel/ordinal.htm

Jim Cone
San Francisco, USA


"Andibevan" wrote in
message ...
Hi All,
Is there a way in excel to format a date so that it automatically includes
the "th" and "st".
I.e. so 1/8/05 -- 1st August 2005?
Ta
Andi

It's a little chunky, but this works:
=(IF(MOD((DAY(A1)),10)=1,DAY(A1)&"st",IF(MOD((DAY( A1)),10)=2,DAY(A1)&"nd",IF(MOD((DAY(A1)),10)=3,DAY (A1)&"rd",DAY(A1)&"th"))))&TEXT(A1,"
mmmm ")&YEAR(A1)

.... where your date is in A1.

Oops! The previously formula fails for the 11th, 12, and 13th. Use
this instead:
=(IF(INT(DAY(A1)/10)=1,DAY(A1)&"th",IF(MOD((DAY(A1)),10)=1,DAY(A1)& "st",IF(MOD((DAY(A1)),10)=2,DAY(A1)&"nd",IF(MOD((D AY(A1)),10)=3,DAY(A1)&"rd",DAY(A1)&"th")))))&TEXT( A1,"
mmmm ")&YEAR(A1)

Nicely done Dave........

Vaya con Dios,
Chuck, CABGx3



"Dave O" wrote:

Oops! The previously formula fails for the 11th, 12, and 13th. Use
this instead:
=(IF(INT(DAY(A1)/10)=1,DAY(A1)&"th",IF(MOD((DAY(A1)),10)=1,DAY(A1)& "st",IF(MOD((DAY(A1)),10)=2,DAY(A1)&"nd",IF(MOD((D AY(A1)),10)=3,DAY(A1)&"rd",DAY(A1)&"th")))))&TEXT( A1,"
mmmm ")&YEAR(A1)