J
jai
Hi,
I am making an attendance sheet, where I have to give leaves if an employee
is absent and he/she has leave balance in his/her account. Presently I am
doing it manually but I am trying to make it automatically. I was making a
formula, but due to many condition the formula become long and still I have
to add 4-5 condition.
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",""))))
I am making an attendance sheet, where I have to give leaves if an employee
is absent and he/she has leave balance in his/her account. Presently I am
doing it manually but I am trying to make it automatically. I was making a
formula, but due to many condition the formula become long and still I have
to add 4-5 condition.
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",""))))