Nested IF formula: categorise dates into appropriate financial quarter

**his5r2m** · 05-26-2009, 10:33 AM

Hello,
I need an nested IF formula that categorises dates, in column A, into the appropriate financial quarter, in column B.

For example:

IF column A=1/4/08 to 30/6/08 (inclusive), then column B = “1st Quarter”;
IF column A= 1/7/08 to 30/9/08 (inclusive), then Column B = “2nd Quarter”;
IF column A= 1/10/08 to 31/12/08 (inclusive), then Column B = “3rd Quarter”;
IF column A= 1/1/09 to 31/3/09 (inclusive), then Column B = “4th Quarter”.

Does anyone know the appropriate syntax?

Many Thanks

Rich

**leto2** · 05-26-2009, 04:58 PM

Hi,

I'm not positive this is what you're looking for. When I opened your workbook, it looked like you were trying to get formulas for Column B in the Asbestos Sheet, so that's what I have below.

I converted dates to values to do the < or > comparisons. Instead of hardcoding, you can just place the dates in another column and point the formula to that column.

=SUMPRODUCT((Incidents!$A$4:$A$500=$A$1)*(Incidents!$C$4:$C$500>=39814)*(Incidents!$C$4:$C$500<39845)*(Incidents!$H$4:$H$500))
=SUMPRODUCT((Incidents!$A$4:$A$500=$A$1)*(Incidents!$C$4:$C$500>=39845)*(Incidents!$C$4:$C$500<39873)*(Incidents!$H$4:$H$500))
=SUMPRODUCT((Incidents!$A$4:$A$500=$A$1)*(Incidents!$C$4:$C$500>=39873)*(Incidents!$C$4:$C$500<39904)*(Incidents!$H$4:$H$500))
...etc

To do a quarter, just change the beginning and end dates.

I put examples in the attached workbook (look at both sheets).

I hope this helps.

**his5r2m** · 05-27-2009, 05:35 AM

Im sorry I seemed to have attached the wrong dummy workbook!
Please find attached the correct one -labelled "dummy workbook financial quarter".
(no wonder it was confusing!)