I am making an attendance sheet, in which I have to put certain condition for
leave management. When I am putting formula, excel shows formula is too long
dialog box.

Formula is below mentioned.

IF(WEEKDAY(($E$2+N$5),2)=1&7,"",IF(AND(OR(IF(WEEKD AY(($IH$23+BX$5),2)=1,COUNTIF(BX7:CC7,"A")=5,COUNT IF(BX7:CC7,"A")=4),IF(WEEKDAY(($IH$23+BY$5),2)=1,C OUNTIF(BY7:CD7,"A")=5,COUNTIF(BY7:CD7,"A")=4),IF(W EEKDAY(($IH$23+BZ$5),2)=1,COUNTIF(BZ7:CE7,"A")=5,C OUNTIF(BZ7:CE7,"A")=4),IF(WEEKDAY(($IH$23+CA$5),2) =1,COUNTIF(CA7:CF7,"A")=5,COUNTIF(CA7:CF7,"A")=4), IF(WEEKDAY(($IH$23+CB$5),2)=1,COUNTIF(CB7:CG7,"A") =5,COUNTIF(CB7:CG7,"A")=4),IF(WEEKDAY(($E$2+CC$5), 2)=1,COUNTIF(CC7:CH7,"A")=5,COUNTIF(CC7:CH7,"A")=4 )),IF(COUNTIF(N7:R7,"PL")=3,$HY7=1,IF(COUNTIF(N7 :R7,"PL")=2,$HY7=2,IF(COUNTIF(N7:R7,"PL")=1,$HY7 =3,$HY7=3)))),"PL",""))&IF(AND(NOT(R7="PL"),NOT(R 7="SL"),NOT(R7="SL/2"),NOT(R7="SL/4"),CC7="A",$HW7=1),"CL",IF(AND(NOT(R7="PL"),NOT( R7="CL"),NOT(R7="CL/2"),NOT(R7="CL/4"),CC7="A",$HX7=1),"SL",IF(AND(NOT(R7="PL"),NOT( R7="SL/2"),NOT(R7="SL"),NOT(R7="SL/4"),CC7="A/2",$HW7=0.5),"CL/2",IF(AND(NOT(R7="PL"),NOT(R7="CL/2"),NOT(R7="CL"),NOT(R7="CL/4"),CC7="A/2",$HX7=0.5),"SL/2",""))))

Perhaps a macro might be a better soluton. Complex formulae are difficult to
understand; with a macro you can add comments as "documentation" to explain
the logic.

You could change NOT(A=B) (general format) to A<B which will shorten your
formula.

"jai" wrote:

I am making an attendance sheet, in which I have to put certain condition for
leave management. When I am putting formula, excel shows formula is too long
dialog box.

Formula is below mentioned.

IF(WEEKDAY(($E$2+N$5),2)=1&7,"",IF(AND(OR(IF(WEEKD AY(($IH$23+BX$5),2)=1,COUNTIF(BX7:CC7,"A")=5,COUNT IF(BX7:CC7,"A")=4),IF(WEEKDAY(($IH$23+BY$5),2)=1,C OUNTIF(BY7:CD7,"A")=5,COUNTIF(BY7:CD7,"A")=4),IF(W EEKDAY(($IH$23+BZ$5),2)=1,COUNTIF(BZ7:CE7,"A")=5,C OUNTIF(BZ7:CE7,"A")=4),IF(WEEKDAY(($IH$23+CA$5),2) =1,COUNTIF(CA7:CF7,"A")=5,COUNTIF(CA7:CF7,"A")=4), IF(WEEKDAY(($IH$23+CB$5),2)=1,COUNTIF(CB7:CG7,"A") =5,COUNTIF(CB7:CG7,"A")=4),IF(WEEKDAY(($E$2+CC$5), 2)=1,COUNTIF(CC7:CH7,"A")=5,COUNTIF(CC7:CH7,"A")=4 )),IF(COUNTIF(N7:R7,"PL")=3,$HY7=1,IF(COUNTIF(N7 :R7,"PL")=2,$HY7=2,IF(COUNTIF(N7:R7,"PL")=1,$HY7 =3,$HY7=3)))),"PL",""))&IF(AND(NOT(R7="PL"),NOT(R 7="SL"),NOT(R7="SL/2"),NOT(R7="SL/4"),CC7="A",$HW7=1),"CL",IF(AND(NOT(R7="PL"),NOT( R7="CL"),NOT(R7="CL/2"),NOT(R7="CL/4"),CC7="A",$HX7=1),"SL",IF(AND(NOT(R7="PL"),NOT( R7="SL/2"),NOT(R7="SL"),NOT(R7="SL/4"),CC7="A/2",$HW7=0.5),"CL/2",IF(AND(NOT(R7="PL"),NOT(R7="CL/2"),NOT(R7="CL"),NOT(R7="CL/4"),CC7="A/2",$HX7=0.5),"SL/2",""))))

Maybe it's time to split the formula into multiple cells and then use those cell
references in the "final" formula.

By the way, I don't think you're gonna get what you want with this portion:

=IF(WEEKDAY(($E$2+N$5),2)=1&7



jai wrote:

I am making an attendance sheet, in which I have to put certain condition for
leave management. When I am putting formula, excel shows formula is too long
dialog box.

Formula is below mentioned.

IF(WEEKDAY(($E$2+N$5),2)=1&7,"",IF(AND(OR(IF(WEEKD AY(($IH$23+BX$5),2)=1,COUNTIF(BX7:CC7,"A")=5,COUNT IF(BX7:CC7,"A")=4),IF(WEEKDAY(($IH$23+BY$5),2)=1,C OUNTIF(BY7:CD7,"A")=5,COUNTIF(BY7:CD7,"A")=4),IF(W EEKDAY(($IH$23+BZ$5),2)=1,COUNTIF(BZ7:CE7,"A")=5,C OUNTIF(BZ7:CE7,"A")=4),IF(WEEKDAY(($IH$23+CA$5),2) =1,COUNTIF(CA7:CF7,"A")=5,COUNTIF(CA7:CF7,"A")=4), IF(WEEKDAY(($IH$23+CB$5),2)=1,COUNTIF(CB7:CG7,"A") =5,COUNTIF(CB7:CG7,"A")=4),IF(WEEKDAY(($E$2+CC$5), 2)=1,COUNTIF(CC7:CH7,"A")=5,COUNTIF(CC7:CH7,"A")=4 )),IF(COUNTIF(N7:R7,"PL")=3,$HY7=1,IF(COUNTIF(N7 :R7,"PL")=2,$HY7=2,IF(COUNTIF(N7:R7,"PL")=1,$HY7 =3,$HY7=3)))),"PL",""))&IF(AND(NOT(R7="PL"),NOT(R 7="SL"),NOT(R7="SL/2"),NOT(R7="SL/4"),CC7="A",$HW7=1),"CL",IF(AND(NOT(R7="PL"),NOT( R7="CL"),NOT(R7="CL/2"),NOT(R7="CL/4"),CC7="A",$HX7=1),"SL",IF(AND(NOT(R7="PL"),NOT( R7="SL/2"),NOT(R7="SL"),NOT(R7="SL/4"),CC7="A/2",$HW7=0.5),"CL/2",IF(AND(NOT(R7="PL"),NOT(R7="CL/2"),NOT(R7="CL"),NOT(R7="CL/4"),CC7="A/2",$HX7=0.5),"SL/2",""))))

--

Dave Peterson