Formatting of dates into quarters

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.

"Peter" <[email protected]> wrote in message
news:[email protected]...
> 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.
>

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.

"Doug Kanter" wrote:

>
> "Peter" <[email protected]> wrote in message
> news:[email protected]...
> > 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.
> >
>
> 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.
>
>
>

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

"Peter" wrote:

> 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.
>
> "Doug Kanter" wrote:
>
> >
> > "Peter" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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.
> > >
> >
> > 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.
> >
> >
> >

Peter,

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

="Q"&INT((MONTH(A1)-1)/3)+1 & " " & YEAR(A1)

HTH,
Bernie
MS Excel MVP


"Peter" <[email protected]> wrote in message
news:[email protected]...
> Alternatively, is there some kind of VBA code to create a customized number
> format?
>
> "Peter" wrote:
>
>> 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.
>>
>> "Doug Kanter" wrote:
>>
>> >
>> > "Peter" <[email protected]> wrote in message
>> > news:[email protected]...
>> > > 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.
>> > >
>> >
>> > 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.
>> >
>> >
>> >

On Tue, 21 Feb 2006 05:37:15 -0800, "Peter" <[email protected]>
wrote:

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

What you want to do cannot be done with formatting.

Understand that in "Excel speak" formatting changes only the appearance of the
cell and does not change the contents.

There is no "format" that will give you qqq-yyyy.




--ron