How to custom format a date

[shriil]

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!

 

[Ron Rosenfeld]

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.

 

[shriil]

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 othercalculations.  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?

 

[Ron Rosenfeld]

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.

 

[shriil]

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!

 

[Ron Rosenfeld]

Wow .. you made it so simple.

Thanks a lot for the advice!

Glad to help. Thanks for the feedback.