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!

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.

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?

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.

On Dec 1, 1:17*am, Ron Rosenfeld wrote:
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.- Hide quoted text -

- Show quoted text -


Wow .. you made it so simple.

Thanks a lot for the advice!

On Wed, 30 Nov 2011 23:36:36 -0800 (PST), shriil wrote:

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.- Hide quoted text -

- Show quoted text -


Wow .. you made it so simple.

Thanks a lot for the advice!

Glad to help. Thanks for the feedback.