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?