Date Formats

[S. Keen]

Is it possible to format a range of date fields in Excel
2000 with their ordinals? ie. 1st, 2nd, 3rd with st, nd,
rd in superscript.

 

[Norman Harker]

Hi S Keen!

AFAIK you can't get it by formatting.

You can use the ordinal formula found at:

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

Here's an example by formula applied to a date in A1:

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

You should note that the result is text and not a date serial number.

You'll also find coverage of ordinal dates at:

Dave McRitchie:
http://www.mvps.org/dmcritchie/excel/datetime.htm#ordinal


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[S.Keen]

Thank you for this - I've tried it and it's working. Sorry
to be picky but is there any way of getting the st, th, rd
and nd suffix in superscript? I've been trying to do this
but have only managed to format the whole cell as
superscript.

Thanks

S Keen

 

[Norman Harker]

Hi S Keen!

Formulas and functions can't change the formatting of a cell. They can
only return values. Conditional formats can depend upon the cell's
value but you can't conditionally format to get the superscript.

It could be achieved by VBA but I've not tried it.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.