Formula won't work...

[ZXCV123]

=IF(L2="Closed",(IF(MONTH($E2)<=3,"Q1",IF(MONTH($E2)<=6,"Q2",IF(MONTH($E2)<=9,"Q3",IF(MONTH($E2)<=12,"Q4",""))))&"-"&if(isblank($J2),Year(I2),$J2),"")

Hi,

Can anyone recognise where the error lies in the formula above? It won't let me enter it.

If you need any more information then let me know.

Thanks

 

[mole999]

=IF(L2="Closed",(IF(MONTH($E2)<=3,"Q1",IF(MONTH($E2)<=6,"Q2",IF(MONTH($E2)<=9,"Q3",IF(MONTH($E2)<=12,"Q4",""))))&"-"&if(isblank($J2),Year(I2),$J2),"")

 

[CharlesChuckieCharles]

One too many brackets



=IF(L2="Closed",(IF(MONTH($E2)<=3,"Q1",IF(MONTH($E2)<=6,"Q2",IF(MONTH($E2)<=9,"Q3",IF(MONTH($E2)<=12,"Q4",""))))&"-"&if(isblank($J2),Year(I2),$J2),"")

 

[Jonmo1]

You have an extra ( in there

=IF(L2="Closed",(IF(MONTH($E2)<=3,"Q1",IF(MONTH($E2)<=6,"Q2",IF(MONTH($E2)<=9,"Q3",IF(MONTH($E2)<=12,"Q4",""))))&"-"&if(isblank($J2),Year(I2),$J2),"")
Should be
=IF(L2="Closed",IF(MONTH($E2)<=3,"Q1",IF(MONTH($E2)<=6,"Q2",IF(MONTH($E2)<=9,"Q3",IF(MONTH($E2)<=12,"Q4",""))))&"-"&if(isblank($J2),Year(I2),$J2),"")

 

[Jonmo1]

Also, here's a much easier way to get the quarter number from a date..

CEILING(MONTH(E2)/3,1)

So you could do
=IF(L2="Closed","Q"&CEILING(MONTH(E2)/3,1)&"-"&IF(ISBLANK($J2),YEAR(I2),$J2),"")

 

[ZXCV123]

Thank you all - I kept missing that but thought I was close!