Thread: 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)