Table function with multiple condition

[Negi1984]

Hi All,

I have created a dummy sheet and uploaded in below mentioned link with desired output which I needed.

https://1drv.ms/x/s!Ap80Ku6M2Tw5gSjnz_4XxSm4Rg5R

Logic : -

1) IF RESOURCE STATUS or ASSIGNMENT STATUS is LEFT PROJECT or OLD-INACTIVE and Hourly rate is 0 then output should be "NA".
2) where RESOURCE STATUS is ENGAGED/APPROVED and ASSIGNMENT STATUS is blank then :-
i) ASSIGNMENT START DATE & END DATE difference and check if all the column should be filled with greater than 0 value. for example : if start date is of jan and end date in mar than column Jan to Mar should be filled with value
greater than 0 and if its satisfy the condition the result should be "ok" , otherwise "issue".

I tried below formula but its not 100% ok.


=IF(OR([@[RESOURCE
STATUS]]="LEFT PROJECT",[@[ASSIGNMENT STATUS]]="OLD-INACTIVE",[@[HOURLY RATE]]=0),"NA",IF(AND([@[RESOURCE
STATUS]]="ENGAGED/APPROVED",[@[ASSIGNMENT STATUS]]="",SUMPRODUCT((ASSIGNMENTS[@[Jan-14]:[Nov-14]]<=0)*(DATEVALUE("1-"&ASSIGNMENTS[[#Headers],[Jan-14]:[Nov-14]])>=[@[ASSIGNMENT START DATE]])*(DATEVALUE("1-"&ASSIGNMENTS[[#Headers],[Jan-14]:[Nov-14]])<=[@[END DATE]]))=0,SUMPRODUCT((ASSIGNMENTS[@[Jan-14]:[Nov-14]]>0)*((DATEVALUE("1-"&ASSIGNMENTS[[#Headers],[Jan-14]:[Nov-14]])<[@[ASSIGNMENT START DATE]])+(DATEVALUE("1-"&ASSIGNMENTS[[#Headers],[Jan-14]:[Nov-14]])>[@[END DATE]])))=0),"Ok","Issue"))

Thanks once again for your valuable suggestion
Regards,
Rajender

 

[Negi1984]

Hi All,

Sorry previous link file was password protected. I uploaded the new one.

looking forward for your valuable suggestion.

https://1drv.ms/x/s!Ap80Ku6M2Tw5gS7NRRnGR11-mf0-