Leave Management

[Guest]

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(WEEKDAY(($IH$23+BX$5),2)=1,COUNTIF(BX7:CC7,"A")=5,COUNTIF(BX7:CC7,"A")=4),IF(WEEKDAY(($IH$23+BY$5),2)=1,COUNTIF(BY7:CD7,"A")=5,COUNTIF(BY7:CD7,"A")=4),IF(WEEKDAY(($IH$23+BZ$5),2)=1,COUNTIF(BZ7:CE7,"A")=5,COUNTIF(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(R7="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",""))))

 

[Guest]

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.

 

[Dave Peterson]

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