Custom fill series?
Maybe this is shorter than yours:
=DAY(A1)&IF(OR(DAY(A1)=1,DAY(A1)=21,DAY(A1)=31),"s t",IF(OR(DAY(A1)=2,DAY(A1)=22),"nd",IF(OR(DAY(A1)= 3,DAY(A1)=23),"rd","th")))&"
"&UPPER(TEXT(A1,"mmmm"))&" "&TEXT(A1,"yyyy")
but it works also with 21, 22, 23 and 31
martinkorner wrote:
Right then...I've got a solution!!
I also realised the problem with 21, 22, 23 and 31.
So I made this formula...It's probably far longer than it needed to
be, but
this works, so I'm happy with it:
---------------------------------
=DAY(A60)&LOOKUP(DAY(A60),{1,2,3,4,5,6,7,8,9,10,11 ,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,2 8,29,30,31},{"st","nd","rd","th","th","th","th","t h","th","th","th","th","th","th","th","th","th","t h","th","th","st","nd","rd","th","th","th","th","t h","th","th","st"})&TEXT(A60,"
mmmm")&TEXT(A60," yyyy")
---------------------------------
It basically specifies the suffix for each individual date.
Martin
"David" wrote:
For me, all offerings failed to provide proper ordinal for days 21,
22, 23, 31
--
David
?B?bWFydGlua29ybmVy?= wrote
I need to have a collumn in my spreadsheet which will display every
Sunday's date.
e.g.
25TH JUNE 2006
1ST JULY 2006
8TH JULY 2006
etc...
I also need the "TH" "ST" ND" and "RD" 's to be in superscript.
I am using Excel 2002 (XP) on a Windows XP Home Edition PC.
Thanks,
Martin
--
(I'm not sure of names of menus, options and commands, because
translating from the Italian version of Excel...)
Hope I helped you.
Thanks in advance for your feedback.
Ciao
Franz Verga from Italy
|