=DAY(B1)&CHOOSE(RIGHT(DAY(B1),1)+1,"th","st","nd", "rd","th","th","th","th","th","th")&" day of "&TEXT(B1,"mmmm, yyyy")

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"ProudFoot" wrote in message ...
| Thanks,
|
| This helps for most of the dates, what should I add if the date is the 1st,
| 2nd, 3rd, 21st, 22nd.....?
|
| "FSt1" wrote:
|
| hi
| not a formula but 3 formulas with concatinators.
| date in b2?......
|
| =DAY(B2)&"th Day of "&TEXT(B2,"mmmm")&", "&YEAR(B2)
|
| regards
| FSt1
|
| "ProudFoot" wrote:
|
| Is there a formula or VBA code to make "July 8, 2008" automatically say "8th
| day of July, 2008"?
|
|
| Any help is appreciated.