Posted to microsoft.public.excel.misc
|
|
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
|