Add st and th to dates

A

Andibevan

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
 
C

CyberTaz

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 |:>)
 
D

Dave O

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.
 
D

Dave O

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((DAY(A1)),10)=3,DAY(A1)&"rd",DAY(A1)&"th")))))&TEXT(A1,"
mmmm ")&YEAR(A1)
 
Top