Silly date/text question - Excel 2k

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi- is there an easy way to show dates/text with the day eg. July 17th
similar to Word 2k format eg July 17th . Currently cut/paste from Word 2k -
to get raised 'th' or 'rd' or 'nd' etc.
TIA
Dan
 
Are you looking for the th, rd, nd stuff or are you looking to make it
superscript?

If you're looking to add the th, rd, nd and if you can use another cell, you can
use a formula from Chip Pearson's site:
http://www.cpearson.com/excel/ordinal.htm
(but that helper cell is now text--no longer a date.)

Actually, you'd need a minor modification to Chip's formula:

=A1&IF(AND(A1>=10,A1<=14),"th",
CHOOSE(MOD(A1,10)+1,"th","st","nd","rd","th","th","th","th","th","th"))

becomes:

=TEXT(A1, "mmmm d") &IF(AND(DAY(A1)>=10,A1<=14),"th",
CHOOSE(MOD(DAY(A1),10)+1,"th","st","nd","rd",
"th","th","th","th","th","th"))

(each of these formulas go in a single cell)

If you were looking to make the th a superscript (after you've typed it in!),
you could format those characters yourself (select them on the formula
bar|Format|Cells|Superscript).

But now that cell is just text, too.

John Walkenbach has an addin that may help you do lots of them:
http://j-walk.com/ss/excel/files/supersub.htm

(and you can't do both--have a formula and have part of it superscript and part
not superscript).
 
Back
Top