On Dec 1, 1:17*am, Ron Rosenfeld <r...@nospam.net> wrote:
> On Wed, 30 Nov 2011 09:51:53 -0800 (PST), shriil <sanjib.lah...@gmail.com> wrote:
> >On Nov 30, 5:58*pm, Ron Rosenfeld <r...@nospam.net> wrote:
> >> On Wed, 30 Nov 2011 00:11:09 -0800 (PST), shriil <sanjib.lah...@gmail.com> 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!
|
