Date format

  • Thread starter Thread starter Smudger
  • Start date Start date
S

Smudger

Hi guys,

This one has been bugging me (& the rest of us in the
office) for some time now!

I want to format a date cell in Excel to show ddth,nd or
st. - ie. 17th Feb.

I can do this with an 'if' and 'or' statement but the
workbook is already getting quite complex.

I can't find this in any book or the help directory.

Cheers

Smudger
 
Hi Smudger!

Using the ordinal formula found at:

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

I can get what you want 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")

AFAIK you can't get it by formatting. 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.
 
Thanks sooooo much for this -Particularly like the use of
the Choose function - Nice One ;o)
 
Hi Smudger!

Thanks for thanks is always appreciated. Hang around and you may see
other solutions posted. I selected one of the easier to understand
methods.

It's always best to try and see if you can do something first. But
don't let it bug you too long. Often, as in this case, a, "Sorry! You
can't do it!" is as good as a solution.

It is a bit strange that Excel will allow you to drag down Ordinal
numbers but doesn't allow a format.

--
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.
 
Back
Top