Formatting of dates into quarters

P

Peter

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

Doug Kanter

Peter said:
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.
Click to expand...

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

Peter

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

Bernie Deitrick

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
 
R

Ron Rosenfeld

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.
Click to expand...

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
 
Top