Nested If function I can seem to get right

[sandman4justice]

Hello,

For my profession I have to keep track of large quantities of documents, and if these documents are not available, I have to request them from our suppliers. I am trying to formulate a more straightforward approach on prioritizing these quest for requests to suppliers.

I have several fields: Estimated time of arrival of a shipment (DATE = C2); Received document 1 (TICK X on E2); Received document 2 (TICK X on F2); Requested for (TICK X on J2)

In a TO DO field I want to retrieve the following information: In want there to appear: "Contact supplier(Opvragen!!)" in case of one of the X-ticks has not been filled in AND if the estimated time of arrival is equal or less then 7 days.

I tried this formula --> =IF((DAYS360(TODAY();C2;TRUE))>=7;"";IF(AND(E2="x";F2="x";J2="x");"";"Opvragen!!")) but it doesn't seem to work the way I want it to

In essence I have to combine these 2 IF statements:

IF(AND(E2="x";F2="x";J2="x");"";"Opvragen!!")
IF((DAYS360(TODAY();C2;TRUE))<=7;"opvragen";"")


These integrations underneath I have tried already, and they all failed (some of them might have false > or < values according to the 7, but we're just for testing)

IF(AND(E2="x";F2="x";J2="x");"";IF((DAYS360(TODAY();C2;TRUE))<=7;"opvragen";""))

IF(AND(E2="x";F2="x";J2="x");"";"Opvragen!!";IF((DAYS360(TODAY();C2;TRUE))<=7;"opvragen";""))

IF(AND(E2="x";F2="x";J2="x";(DAYS360(TODAY();C2;TRUE)<=7));"";"Opvragen!!")

IF(AND(E2="x";F2="x";J2="x";(DAYS360(TODAY();C2;TRUE)>=7));"";"Opvragen!!")

IF(AND(E2="x";F2="x";J2="x";((DAYS360(TODAY();C2;TRUE))<=7));"";"Opvragen!!")

IF((DAYS360(TODAY();C2;TRUE))<=7;"";IF(AND(E2="x";F2="x";J2="x");"";"Opvragen!!"))

IF((DAYS360(TODAY();C2;TRUE))>=7;"";IF(AND(E2="x";F2="x";J2="x");"";"Opvragen!!"))


So long hours later I give up, please help me

[rylo]

Hi

How about
=IF(AND(C2<=TODAY()+7,COUNTA(E2,F2,J2)<3),"Opvragen!!","")

rylo

[tigeravatar]

Alternate
=IF(AND(AND(E2="x",F2="x",J2="x"),ABS(TODAY()-C2)>7),"","Opvragen!!")

[sandman4justice]

Thank you very much. That looks much more simple indeed and it works!