software

Hi folks,

how can I change a concrete date, e.g. 21.02.2006 (typical German format)
into a quarterly view, e.g. Q1 2006 by using a custom number format?

It is essential for me not to use some kind of formula but to simply create
a number format that the user can select.

Thanks in advance.
quot;Peterquot; gt; wrote in message
...
gt; Hi folks,
gt;
gt; how can I change a concrete date, e.g. 21.02.2006 (typical German format)
gt; into a quarterly view, e.g. Q1 2006 by using a custom number format?
gt;
gt; It is essential for me not to use some kind of formula but to simply
gt; create
gt; a number format that the user can select.
gt;
gt; Thanks in advance.
gt;

Perhaps I haven't had enough coffee yet, and may not be thinking creatively,
but formats cannot evaluate a date. You're still going to need some method
for the sheet to know.....what time it is, relative to the beginnings and
ends of quarters.
Maybe this is more concrete.

If I enter a date such as 21.02.2006, then its initial format is dd.mm.yyyy.

But if I want to show the month and the year only, I can change the format
to mmm yyyy, and the cell will return Feb 2006. This is easy.

What I am looking for is a format such as qqq yyyy, that would return Qtr1
2006 or q yyyy, that would return Q1 2006. I need the calendar quarters,
hence January through March equal Quarter 1 of a given year etc.

quot;Doug Kanterquot; wrote:

gt;
gt; quot;Peterquot; gt; wrote in message
gt; ...
gt; gt; Hi folks,
gt; gt;
gt; gt; how can I change a concrete date, e.g. 21.02.2006 (typical German format)
gt; gt; into a quarterly view, e.g. Q1 2006 by using a custom number format?
gt; gt;
gt; gt; It is essential for me not to use some kind of formula but to simply
gt; gt; create
gt; gt; a number format that the user can select.
gt; gt;
gt; gt; Thanks in advance.
gt; gt;
gt;
gt; Perhaps I haven't had enough coffee yet, and may not be thinking creatively,
gt; but formats cannot evaluate a date. You're still going to need some method
gt; for the sheet to know.....what time it is, relative to the beginnings and
gt; ends of quarters.
gt;
gt;
gt;

Alternatively, is there some kind of VBA code to create a customized number
format?

quot;Peterquot; wrote:

gt; Maybe this is more concrete.
gt;
gt; If I enter a date such as 21.02.2006, then its initial format is dd.mm.yyyy.
gt;
gt; But if I want to show the month and the year only, I can change the format
gt; to mmm yyyy, and the cell will return Feb 2006. This is easy.
gt;
gt; What I am looking for is a format such as qqq yyyy, that would return Qtr1
gt; 2006 or q yyyy, that would return Q1 2006. I need the calendar quarters,
gt; hence January through March equal Quarter 1 of a given year etc.
gt;
gt; quot;Doug Kanterquot; wrote:
gt;
gt; gt;
gt; gt; quot;Peterquot; gt; wrote in message
gt; gt; ...
gt; gt; gt; Hi folks,
gt; gt; gt;
gt; gt; gt; how can I change a concrete date, e.g. 21.02.2006 (typical German format)
gt; gt; gt; into a quarterly view, e.g. Q1 2006 by using a custom number format?
gt; gt; gt;
gt; gt; gt; It is essential for me not to use some kind of formula but to simply
gt; gt; gt; create
gt; gt; gt; a number format that the user can select.
gt; gt; gt;
gt; gt; gt; Thanks in advance.
gt; gt; gt;
gt; gt;
gt; gt; Perhaps I haven't had enough coffee yet, and may not be thinking creatively,
gt; gt; but formats cannot evaluate a date. You're still going to need some method
gt; gt; for the sheet to know.....what time it is, relative to the beginnings and
gt; gt; ends of quarters.
gt; gt;
gt; gt;
gt; gt;

Peter,

You can't do it with formats. You could use a formula:

=quot;Qquot;amp;INT((MONTH(A1)-1)/3) 1 amp; quot; quot; amp; YEAR(A1)

HTH,
Bernie
MS Excel MVPquot;Peterquot; gt; wrote in message
...
gt; Alternatively, is there some kind of VBA code to create a customized number
gt; format?
gt;
gt; quot;Peterquot; wrote:
gt;
gt;gt; Maybe this is more concrete.
gt;gt;
gt;gt; If I enter a date such as 21.02.2006, then its initial format is dd.mm.yyyy.
gt;gt;
gt;gt; But if I want to show the month and the year only, I can change the format
gt;gt; to mmm yyyy, and the cell will return Feb 2006. This is easy.
gt;gt;
gt;gt; What I am looking for is a format such as qqq yyyy, that would return Qtr1
gt;gt; 2006 or q yyyy, that would return Q1 2006. I need the calendar quarters,
gt;gt; hence January through March equal Quarter 1 of a given year etc.
gt;gt;
gt;gt; quot;Doug Kanterquot; wrote:
gt;gt;
gt;gt; gt;
gt;gt; gt; quot;Peterquot; gt; wrote in message
gt;gt; gt; ...
gt;gt; gt; gt; Hi folks,
gt;gt; gt; gt;
gt;gt; gt; gt; how can I change a concrete date, e.g. 21.02.2006 (typical German format)
gt;gt; gt; gt; into a quarterly view, e.g. Q1 2006 by using a custom number format?
gt;gt; gt; gt;
gt;gt; gt; gt; It is essential for me not to use some kind of formula but to simply
gt;gt; gt; gt; create
gt;gt; gt; gt; a number format that the user can select.
gt;gt; gt; gt;
gt;gt; gt; gt; Thanks in advance.
gt;gt; gt; gt;
gt;gt; gt;
gt;gt; gt; Perhaps I haven't had enough coffee yet, and may not be thinking creatively,
gt;gt; gt; but formats cannot evaluate a date. You're still going to need some method
gt;gt; gt; for the sheet to know.....what time it is, relative to the beginnings and
gt;gt; gt; ends of quarters.
gt;gt; gt;
gt;gt; gt;
gt;gt; gt;
On Tue, 21 Feb 2006 05:37:15 -0800, quot;Peterquot; gt;
wrote:

gt;Hi folks,
gt;
gt;how can I change a concrete date, e.g. 21.02.2006 (typical German format)
gt;into a quarterly view, e.g. Q1 2006 by using a custom number format?
gt;
gt;It is essential for me not to use some kind of formula but to simply create
gt;a number format that the user can select.
gt;
gt;Thanks in advance.

What you want to do cannot be done with formatting.

Understand that in quot;Excel speakquot; formatting changes only the appearance of the
cell and does not change the contents.

There is no quot;formatquot; that will give you qqq-yyyy.

--ron