MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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


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)



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


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.