How do i get th,nd,st,rd to display in Excel

[Guest]

Hi,

I am trying to get the date, entered as 06/04/2002 to display as Saturday
6th Apr 2002

I have got as far as Saturday 6 Apr 2002 with the custom format: dddd d mmm
yyy

What do I enter in the Custom category of the dialogue box that opens when I
go to Format>Cells?

I would like to have the "th","st","rd","nd" displayed, by entering
something similar to "dddd d mmm yyy" in the custom category of the format
cells dialogue box.

Thanks,

Phil

 

[David Biddulph]

http://www.cpearson.com/excel/ordinal.htm looks a good starting point.

 

[Niek Otten]

Hi Phil,

See

http://xldynamic.com/source/xld.RANK.html

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Hi,
|
| I am trying to get the date, entered as 06/04/2002 to display as Saturday
| 6th Apr 2002
|
| I have got as far as Saturday 6 Apr 2002 with the custom format: dddd d mmm
| yyy
|
| What do I enter in the Custom category of the dialogue box that opens when I
| go to Format>Cells?
|
| I would like to have the "th","st","rd","nd" displayed, by entering
| something similar to "dddd d mmm yyy" in the custom category of the format
| cells dialogue box.
|
| Thanks,
|
| Phil

 

[SteveW]

So I ask a question in regard to your last post and you re-post the whole
thing
as a New post, totally ignoring the replies that you got the last time.

At least you've posed the question clearly and consisely, unlike the last
vague request.

I still don't know how it's done, but a couple of replies seem to have an
answer.
Lets hope you read them.

Steve

 

[Guest]

Is there no easy way of typing something straight into the custom part of
Format Cells. These ways involve formulas, instead of a symply cell
formatting.

 

[Niek Otten]

<Is there no easy way of typing something straight into the custom part of Format Cells>

No.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Is there no easy way of typing something straight into the custom part of
| Format Cells. These ways involve formulas, instead of a symply cell
| formatting.
|
| "SteveW" wrote:
|
| > So I ask a question in regard to your last post and you re-post the whole
| > thing
| > as a New post, totally ignoring the replies that you got the last time.
| >
| > At least you've posed the question clearly and consisely, unlike the last
| > vague request.
| >
| > I still don't know how it's done, but a couple of replies seem to have an
| > answer.
| > Lets hope you read them.
| >
| > Steve
| >
| >
| > On Wed, 15 Nov 2006 08:35:02 -0000, Phil <[email protected]>
| > wrote:
| >
| > > Hi,
| > >
| > > I am trying to get the date, entered as 06/04/2002 to display as Saturday
| > > 6th Apr 2002
| > >
| > > I have got as far as Saturday 6 Apr 2002 with the custom format: dddd d
| > > mmm
| > > yyy
| > >
| > > What do I enter in the Custom category of the dialogue box that opens
| > > when I
| > > go to Format>Cells?
| > >
| > > I would like to have the "th","st","rd","nd" displayed, by entering
| > > something similar to "dddd d mmm yyy" in the custom category of the
| > > format
| > > cells dialogue box.
| > >
| > > Thanks,
| > >
| > > Phil
| >

 

[SteveW]

Annoyingly it seems there isn't - pity

Steve

Is there no easy way of typing something straight into the custom partof
Format Cells. These ways involve formulas, instead of a symply cell
formatting.

 

[starmuneer]

=(TEXT(TODAY(),"dddd,"))&" the "&TEXT(TODAY(),"d")&LOOKUP(DAY(TODAY()),{1,2,3,4,21,22,23,24,31;"st","nd","rd","th","st","nd","rd","th","st"})&TEXT(TODAY()," of mmmm, yyyy")

Today is 14/01/2013, and hence the result would be displayed as
Monday, the 14th of January, 2013