Date Formats
Posted by Richie Turner on April 17, 2001 9:07 AM
This is a bit of a long shot.
Is there an easy way a date can be formatted so that the old fashioned British "st/nd/rd/th" suffix to the day number can be displayed. eg "17th April 2001" rather than "17 April 2001"
You'll be a hero in these parts if you've got a solution.
Posted by Aladin Akyurek on April 17, 2001 9:37 AM
Richie
This is a quick shot.
=DAY(A1)&VLOOKUP(DAY(A1),{1,"st";2,"nd";3,"rd";4,"th"},2,1)&" "&VLOOKUP(MONTH(A1),{1,"January";2,"February";3,"March";4,"April";5,"May";6,"June";7,"July";8,"August";9,"September";10,"October";11,"November";12,"December"},2,0)&" "&YEAR(A1)
Aladin
===============
Posted by Tempus Ordinalis on April 17, 2001 9:55 AM
=DAY(A1)&IF(INT(MOD(DAY(A1),100)/10)=1, "th", IF(MOD(DAY(A1),10)=1, "st",IF(MOD(DAY(A1),10)=2,"nd", IF(MOD(DAY(A1),10)=3, "rd","th"))))& " " &TEXT(A1,"mmmm, yyyy")
Unfortunately the result is Text, not a Date !
Posted by lenze on April 17, 2001 10:56 AM
Richie:
Select the cells you wish to format and then choose cells format. On the number tab, select custom. In the the box for type, enter the following: dd"th"-Mmmm-yyyy and click OK.
Posted by lenze on April 17, 2001 11:07 AM
OPPS: I didn't read your post correctly. To get days like the first and second to work, you will have to use some form of custom conditional formatting based on the day value.
lenze
