On Wed, 30 Nov 2011 00:11:09 -0800 (PST), shriil wrote:

Hi

I would like to custom format a date through a formula such that the
following dates can be formatted
as mentioned below

1-Sep-11 : 1st September 2011

2- Sep-11 : 2nd September 2011

3- Sep -11 : 3rd September 2011

4-Sep-11 : 4th September 2011

The "st", "nd", "rd" & "th" needs also to be superscripted with the
Day number.

Thanks for any ideas!

You need to make some decisions before we can advise. You cannot do all you want with formatting.

if you MUST have the superscripting of the "st", etc, you will NOT be able to retain the value as a date, and you will need to accomplish this task with a VBA macro.
It will need to be a string and will not be directly useable in any other calculations. Also, although you could copy/paste and preserve the formatting, trying to set another cell equal to that cell, e.g. A2: =A1 where A1 contains the string formatted with the superscripted "st" will not retain the superscript in A2.

If you have a version of Excel 2007 or later, and do NOT require the superscript, then this can be accomplished with custom and conditional formatting.