mardi 30 juin 2015

Complex "IF", "OR", "AND" functions in Google Spreadsheet

here is what I have done so far: (See screenshot attached) When Something is added under "Laden" it gets a certain status. If this status is either "Warte auf GO" or "Warte auf Daten" AND the Date in the Datefield D2 is more than 6 days ago, I want the color of the Cell under "Laden" which has now "Baretta" in it to become yellow. To do so, I have added the simple function:

=OR(AND(L4="warte auf GO"; 6<DAYS360(D4; today())); AND(L4="warte auf DATEN"; 6<DAYS360(D4; today())))

And used conditional formatting. So if this returns true, color A2 in yellow. This worked.

But now to the problem: Secondly if the field turns yellow, I have three more "reminder"-cells. So the first stage is (1) I have been reminded with a yellow field A2 and put the date I have reacted on the reminder in field N2. If 6 days are over, A2 turns yellow again. (2) So I have been reminded with a yellow field A2 for the second time and put the date I have reacted on the reminder in field O2. If 6 days are over, A2 turns yellow again. (3) Again like (2).

So basically I use this sheet as a "reminder"-system with different stages. I have tried to solve it with the following formular. It does return some "false" and "trues", but not in an order, that I fully understand. I guess this is a logical problem, but I do not really see it.

Here is the formular i used:

=IF(AND(OR(L4="warte auf GO"; L4="warte auf DATEN"); 6<DAYS360(D4; today()); (OR(N4=""; AND

(6

Again without code =IF(AND(OR(L4="warte auf GO"; L4="warte auf DATEN"); 6

Can you help me on that one? Thanks Flo

Aucun commentaire:

Enregistrer un commentaire