'st' 'nd' 'rd' 'th' Format on Date

[R1chard]

How can I format a cell to display the date in this format. Currently it will just display the number, 16 October 2007.

I want it to display 16th October 2007

Thanks

 

[Oaktree]

See:

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

 

[R1chard]

is there not a more basic cell format I could use?

My cell formula is =Today()

I want the date to display as 1st, 2nd etc rather than 1, 2.

Your reply seems to much for what I need.

Thanks for the help

 

[Oaktree]

I don't think there's an easier way than to use

=DAY(TODAY())&IF(AND(MOD(DAY(TODAY()),100)>=10,MOD(DAY(TODAY()),100)<=14),"th",
CHOOSE(MOD(DAY(TODAY()),10)+1,"th","st","nd","rd","th","th","th","th","th","th")) &TEXT(TODAY()," MMMM YYYY")

instead of =TODAY()

Note that this will cause issues if you try to use it in future calculations (e.g. 5 days from now with = A1+5 won't work...)

 

[R1chard]

thats great.

Thanks for your help.

 

[barry houdini]

This is a little shorter...

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

 

[JazzSP8]

You wouldn't believe how many hours I've wasted trying to get that into one formula...

Sheesh .. Gonna have to find me a new past time...

 

[00BrianJ]

Is there a way to achieve this using- Right click, custom formatting? I like to have; dddd, dd mmmm, Wednesday, 24th June 2015

 

[Rick Rothstein]

This is a little shorter...

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

And a little shorter still...

=DAY(TODAY())&MID("thstndrdth",MIN(9,2*RIGHT(A1)*(MOD(A1-11,100)>2)+1),2)&TEXT(TODAY()," mmmm yyyy")

 

[barry houdini]

Is there a way to achieve this using- Right click, custom formatting? I like to have; dddd, dd mmmm, Wednesday, 24th June 2015

You can't do that directly with custom formats, but in Excel 2007 or later versions you can use a combination of conditional formatting and custom formats to achieve that effect, see this post by T. Valko (Biff)