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

[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

 

[Pete_UK]

Try this in A2:

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

Hope this helps.

Pete

 

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

 

[MJW]

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

Much appreciated,

Jamie

 

[Pete_UK]

Thanks for feeding back, Jamie - glad it worked for you.

Pete