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


G

Guest

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
 
Ad

Advertisements

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")
 
G

Guest

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

Much appreciated,

Jamie
 
Ad

Advertisements


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

Similar Threads


Top