'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

 

[deletedalien]

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

no

 

[Tetra201]

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")

Another solution:

=DAY(TODAY())&LOOKUP(MOD(MOD(DAY(TODAY()),30),20),{0,1,2,3,4},{"th","st","nd","rd","th"})&TEXT(TODAY()," mmmm yyyy")