Date format

S

SMILE

Hi
Is there any way to add "st" or "nd"or "th" with the dat
automatically.
For eg. if I enter a date 31-12-04 , I want it to be displayed as 31s
Dec 2004. or if I enter 30-09-04 it should show as 30th Sep 2004.
Appreciate your help
Tom
 
H

Harlan Grove

SMILE > said:
Is there any way to add "st" or "nd"or "th" with the date
automatically.
For eg. if I enter a date 31-12-04 , I want it to be displayed as
31st Dec 2004. or if I enter 30-09-04 it should show as 30th Sep
2004.
Click to expand...

You can't do this with number formats, so these dates would need to be
derived text. Something like

=TEXT(DateEntry,"d"&IF(OR(ABS(DAY(A17)-15)<=5,
ABS(MOD(DAY(A17),10)-2)>1),"\t\h",CHOOSE(MOD(DAY(A17),10),
"\s\t","\n\d","\r\d"))&" mmm yyyy")

but the display would be in another cell containing this formula. Unless you
want to use a Change event handler to alter date entries in place, you can't
enter 31-12-04 and get ordinal days.
 
Top