View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.misc
Bernie Deitrick
 
Posts: n/a
Default Formatting of dates into 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" wrote in message
...
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" wrote in message
...
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.