date format

  • Thread starter Thread starter SMILE
  • Start date Start date
S

SMILE

Hi everyone

Is there any way to add automatically "rd" or "st" or "th" etc after
the date.
I have a date in A1 as "30-01-2001" and I need to show in B1 as
"January 30th"

Hope someone has an idea
Thanks
Toms
 
Hi SMILE!

One Way. Use:

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

But this is text and not a date. Formatting retains the date as a date
serial number.

The base for getting the ordinal number was taken from:

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

--
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.
 
SMILE,

Here's a shorter alternative

=DAY(A1)&CHOOSE(AND(DAY(A1)<>{11,12,13})*MIN(4,MOD(DAY(A1),10))+1,"th","st",
"nd","rd","th")&" "&TEXT(A1,"mmmm")

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Sorry,

I followed Norman's reply, not the question . Therefore my formula should be

=TEXT(A1,"mmmm")&"
"&DAY(A1)&CHOOSE(AND(DAY(A1)<>{11,12,13})*MIN(4,MOD(DAY(A1),10))+1,"th","st"
,"nd","rd","th")

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Back
Top