Formula or Function for quarters
 

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

Formula or Function for quarters
 

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

Formula or Function for quarters
 

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

Formula or Function for quarters
 

On Apr 20, 6:51 pm, Paul Martin wrote:
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 -----

On Apr 20, 6:51*pm, Paul Martin wrote:
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

Formula or Function for quarters
 

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

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

Formula or Function for quarters
 

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



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

Formula or Function for quarters
 

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

On Apr 21, 7:32 pm, Dave Peterson wrote:
Or using a single digit for the quarter:
=YEAR(A1)*10+CEILING(MONTH(A1),3)/3
with a custom format of:
0000\Q0



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