Ordinal Dates and numbers (st,nd,rd,th)

I was just looking at a formula for ordinal dates, which contained 7 references to cell A1, I came up with a better and more flexible way :-

=SUBSTITUTE(TEXT($A$1,"mmmm dzz, yyyy"),"zz",MID("thstndrdthththththth",2*MOD(DAY($A$1),10)*(INT(DAY($A$1)/10)<>1)+1,2))

for April 1st,2010

=SUBSTITUTE(TEXT($A$1,"dzz of mmmm yyyy"),"zz",MID("thstndrdthththththth",2*MOD(DAY($A$1),10)*(INT(DAY($A$1)/10)<>1)+1,2))

for 1st April 2010

=SUBSTITUTE(TEXT($A$1,"dddd t\h\e dzz of mmmm yyyy"),"zz",MID("thstndrdthththththth",2*MOD(DAY($A$1),10)*(INT(DAY($A$1)/10)<>1)+1,2))

for Thursday the 1st of April 2010

=SUBSTITUTE(TEXT($A$1,"dddd mmmm dzz, yyyy"),"zz",MID("thstndrdthththththth",2*MOD(DAY($A$1),10)*(INT(DAY($A$1)/10)<>1)+1,2))

for Thursday April 1st, 2010

if you just wish to use it for any number to ordinal

=A1&MID("thstndrdthththththth",2*MOD(A1,10)*(INT(MOD(A1,100)/10)<>1)+1,2)

Rather annoyingly I managed to decrease the references to the original cell again, every other version I have found on the web had 4 references to add an ordinal I have got that down to 1 which makes it easier to maintain!

=a1&MID(SUBSTITUTE(REPT("thstndrdthththththth",10),"stndrd","ththth",2),MOD(a1,100)*2+1,2)

Here's an even simpler version for numbers (not dates):

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

Can be incorporated into date processing, too:

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

=TEXT(A1,"DDDD, ")&DAY(A1)&MID("thstndrdth",MIN(9,2*RIGHT(DAY(A1))*(MOD(DAY(A1)-11,100)>2)+1),2)&TEXT(A1," MMMM, YYYY")

=TEXT(A1,"DDDD, ")&DAY(A1)&MID("thstndrdth",MIN(9,2*RIGHT(DAY(A1))*(MOD(DAY(A1)-11,100)>2)+1),2)&TEXT(A1," of MMMM YYYY")

=TEXT(A1,"DDDD")&" the "&DAY(A1)&MID("thstndrdth",MIN(9,2*RIGHT(DAY(A1))*(MOD(DAY(A1)-11,100)>2)+1),2)&TEXT(A1," of MMMM, YYYY")

Related:

http://www.excelforum.com/showthread.php?t=926375