Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 37
Default How to custom format a date

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!

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,045
Default How to custom format a date

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.

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 37
Default How to custom format a date

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?
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,045
Default How to custom format a date

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.
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 37
Default How to custom format a date

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!



  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,045
Default How to custom format a date

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.
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
custom date format Tonso Excel Discussion (Misc queries) 2 April 12th 10 01:14 PM
Custom Date Format SJT Excel Discussion (Misc queries) 3 March 1st 10 09:29 AM
Need help with converting CUSTOM format/TEXT format to DATE format Deo Cleto Excel Worksheet Functions 6 June 2nd 09 08:14 PM
Looking for custom date format Pierre Excel Worksheet Functions 15 June 21st 07 03:17 PM
Custom format date bach New Users to Excel 5 August 30th 05 01:43 AM


All times are GMT +1. The time now is 03:20 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"