Column value for row = fiscal quarter (determined by date field)

M

MJW

Hi All,

I have a sheet (roughly 6k rows) in which I've inserted a column to assign a
fiscal quarter label dependent on the date in the column following it. Eg:
A B C etc....
Fiscal Quarter Date Data
(dependent on B) 8/7/07 Case data and other irrelevants...

I attempted doing a multi-nested IF statement to assign "Q1 07", "Q2 07",
"Q3 07" or "Q4 07" depending on the date range and no matter what the actual
related date is, it invariably results in "Q1 07".

=IF(12/2007<A3>4/2007, "Q1 07", IF(3/2007<A3>7/2007, "Q2 07",
IF(6/2007<A3>10/2007, "Q3 07", IF(9/2007<A3>1/2008, "Q4 07"))))

(I also tried this using the DATEVALUE format for the dates, just in case
Excel wasn't reading my ranges as dates. No go with that either.)

I'm sure there's an easy way to do this, I'm just not finding it.

Thanks,

Jamie
 
P

Pete_UK

Try this in A2:

="Q"&INT((MONTH(B2)-1)/3)+1&" "&TEXT(B2,"yy")

Hope this helps.

Pete
 
J

joeu2004

I have [...]
A B C etc....
Fiscal Quarter Date Data
(dependent on B) 8/7/07 Case data and other irrelevants...

I attempted doing a multi-nested IF statement to assign "Q1 07",
"Q2 07", "Q3 07" or "Q4 07" depending on the date range and no
matter what the actual related date is, it invariably results in
"Q1 07".

=IF(12/2007<A3>4/2007, "Q1 07", IF(3/2007<A3>7/2007, "Q2 07",
IF(6/2007<A3>10/2007, "Q3 07", IF(9/2007<A3>1/2008, "Q4 07"))))

You cannot express a range condition (x < y < z) that way -- or even
the way I did it parenthetically. You need to use the AND()
function. For example: and(1<=month(A3), month(A3)<=3).

However, there is a simpler way to formulate the IF() expression so
that you avoid complex comparisons. For example:

=if(month(A3)>=10, "Q4 07", if(month(A3)>=7, "Q3 07", if(month(A3)>=4,
"Q2 07", "Q1 07")))

Alternatively:

="Q" & 1+int((month(A3)-1)/3) & " 07"

And more generally:

="Q" & 1+int((month(A3)-1)/3) & " " & text(year(A3),"yy")
 
M

MJW

Thanks Pete, that's exactly what I was looking for--worked like a charm!

Much appreciated,

Jamie
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top