On Wed, 30 Nov 2011 09:51:53 -0800 (PST), shriil wrote:

On Nov 30, 5:58*pm, Ron Rosenfeld wrote:
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.


Thanks for the help. If I ignore the superscripting part how do I go
about formatting the same?

For Excel 2007+, you can use conditional formatting.

Let us assume you are formatting M1

Select M1
Format/cells/Number/Custom Type: dt\h mmmm yyyy

Then select Conditional Formatting:
New Rule
Formula: =OR(DAY(M1)=3,DAY(M1)=23)
Format/Number/Custom Type: d\r\d mmmm yyyy

New Rule
Formula: =OR(DAY(M1)=2,DAY(M1)=22)
Format/Number/Custom/Type: d\n\d mmmm yyyy

New Rule
Formula: =OR(DAY(M1)=1,DAY(M1)=21,DAY(M1)=31)
Format/Number/Custom/Type: d\st mmmm yyyy

You can copy/paste the format to whatever cells you wish.