Date formatting

  • Thread starter Thread starter YetAnotherAndy
  • Start date Start date
Y

YetAnotherAndy

I need to find a way to format a set of date as the 1st, 2nd, 3rd etc s
it will look like this - 1st December 2002 not 01 December 2002.

I cannot find a way to do this but i know excel recognises thi
format.

Please help
 
Hi YetAnotherAndy!

AFAIK Excel doesn't recognise that format.

You can apply the following to a date in A1:

=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")&" "&YEAR(A1)

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

Again, a shorter alternative to Norman's solution

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

--

HTH

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