View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.misc
Dave Peterson Dave Peterson is offline
external usenet poster
 
Posts: 35,218
Default Formula or Function for quarters

Just to add to Sandy's formula...

I'd use a formula like:
=YEAR(A1)*100+CEILING(MONTH(A1),3)/3

The results would be numeric (200802) and the sorts would work nicely--And I
like the 4 digit year.

And the OP could give the cell a custom format of:
0000\Q00
(format|cells|number tab|custom category).

The cell would display as: 2008Q02
but the value would still be numeric.

Sandy Mann wrote:

Paul,

I think that:

="Q"&CEILING(MONTH(A1),3)/3&TEXT(A1,"yy")

will give you what you want. If by:

column, and I want that they be recognised numerically,


you mean sorting in a numerical order then sort on the original date column.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk

"Paul Martin" wrote in message
...
I would like to be able to have a formula that I can copy down a
column, and I want that they be recognised numerically, eg Q406 would
come before Q107.


On Apr 21, 12:44 pm, "Fred Smith" wrote:
Why not just enter the quarter number -- 1, 2, 3 or 4?

Or, if you want a function, what's it based on -- a date, a month number
or
something else?

Regards,
Fred.

"Paul Martin" wrote in message

...

Hi guys

I'm wondering if there's a formula or function that I can enter into a
column that will return quarters.

* Currently, I have Q106 (meaning first quarter, 2006), Q206, Q306,
Q406, Q107, etc.
* I want the value of the cell to have a numeric value so that the MAX
in the column (ie, the most recent quarter) can be ascertained
numerically.
* It seems I need a function that uses a base of 4. I note that the
LOG function allows the use of a user-defined base, but LOG is not
what I'm after.

Any suggestions are appreciated

Paul Martin
Melbourne, Australia




--

Dave Peterson