Formula or Function for quarters

[Paul Martin]

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

 

[Fred Smith]

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]

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.

 

[joeu2004]

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.

I am quite certain you do not need (or want) to use LOG. I suspect
the MOD function might work for you. But since I don't fully
understand your question, I cannot be more specific.

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.

Does the following array formula solve your problem (commit with ctrl-
shift-Enter)?

=max(value(RIGHT(A1:A4,3)))

PS: The more common notation for fiscal quarters is 1Q06, 2Q06, etc.
But that might complicate the solution.


----- original posting -----

 

[Sandy Mann]

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Dave Peterson]

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.

 

[Dave Peterson]

Or using a single digit for the quarter:
=YEAR(A1)*10+CEILING(MONTH(A1),3)/3
with a custom format of:
0000\Q0

 

[Paul Martin]

Thanks all for your suggestions. The solution I've gone with is to
create a custom list (which I've also used as a named range) and to
use the MATCH function to assign an ascending numeric value to
consecutive quarters. It's simple and works fine.

Paul