Trigger a FLAG based on 2 Conditions (with 1 Condition being a menu selected Choice)

Rick Girard

Hi,

I'm attempting to trigger a Flag based on a Condition from a Menu Select column + a Condition that counts Days.

What I'm trying to do:

If the Menu column = Approved (as is) AND Days column equals any Count , the Flag is White (0) condition.

If the Menu column does not equal Approved (as is) AND Days column is equal to or greater than 5 , the Flag is Red (0) condition.

I used variations of this formula and I am missing some logic somewhere.... I attempted to use an OR statement to vary between the an "Approved" selection and other than "Approved" selection but its not working correctly.

-Appreciate any guidance !

=IF(AND([Approver Business Unit]@row <> "Approved (as is)", [Days Open Since Approval Request]@row >= 5), 1, OR(IF(AND([Approver Business Unit]@row = "Approved (as is)", [Days Open Since Approval Request]@row >= 5), 0)))

Rick Girard

Problem solved with this formula, treated the False condition as a new IF statement to flag the alternative condition.

I set the 1st condition to compare the days open '>5' and if status didnt contain 'Approved', (true) , flag (red) is set, if False, go to next IF and compare days open '>0' and status contained 'Approved' (true) , flag (white) is set. I left the 2nd IF with an no false condition setting as i didnt need it since the other conditions were reflected in the first IF statement..

=IF(AND([Days Open Since Approval Request]@row > 5, [Approver BU Status (menu)]@row <> "Approved (as is)"), 1, IF(AND([Days Open Since Approval Request]@row > 0, [Approver BU Status (menu)]@row = "Approved (as is)"), 0))

Paul Newcome

1 is "true" meaning the flag is present. 0 is false meaning the flag is not there.

Paul Newcome

Right. There are two sets of true/false that you are dealing with here. One true/false set is for the flag and the other is for the IF function. Where you place the 1 and 0 is going to depend on exactly how you write your IF. If you write the IF using the opposite logic, then you would swap the 1 and 0.

Paul Newcome

Try this...

=IF(OR(AND([Approver Business Unit]@row <> "Approved (as is)", [Days Open Since Approval Request]@row >= 5), AND([Approver Business Unit]@row = "Approved (as is)", [Days Open Since Approval Request]@row >= 1)), 0, 1)

Rick Girard

seems to just trigger on the Day count 5 regardless of the Menu select choice..

if nothing is in the Menu select (blank) it Flags Red ...

Paul Newcome

Based on the data in your sheet, the formula is working as expected based on this portion of your original post...

"If the Menu column = Approved (as is) AND Days column equals any Count , the Flag is White (0) condition.

If the Menu column does not equal Approved (as is) AND Days column is equal to or greater than 5 , the Flag is Red (0) condition."

The only portion of the above that could be misconstrued is that "red" does not equal zero (second bit). I have it outputting zero. Are you able to clarify your criteria?

Rick Girard

I'll chk it out and follow up, thanks again.

Rick Girard

Paul,

Still having issues, so I'm trying to simplify the logic get 1 set of conditions to work to start:

Recap:

The column - Approver Business Unit has 4 options 1) Approved (as is) and 3 other text named options.

The column - Days Open Since Approval Request - counts Days from the column - Approval Request Submitted date.

Objective:

I want to change the Flag to White (State=0) if the conditions are met (TRUE) or change the Flag to Red (state=0) if either conditions aren't met (false).

Formula:

=IF(AND([Approver Business Unit]@row = "Approved (as is)", [Days Open Since Approval Request]@row > 5), 0, 1)

my assumption is if conditions are met, then '0' is the True value and '1' is the False value if conditions aren't met.

Result:

I was expecting a White Flag with both conditions (true) and that appeared to work (Item A). However, the Flag stays White regardless of the status change (ie. other than 'Approved') or if I reduce the days counted to less than 5 , the Flag remains White (Item B).

btw: I don't get any errors from the formula so I'm puzzled why this logic isn't working...

Item A:

Item B:

Rick Girard

Problem solved with this formula, treated the False condition as a new IF statement to flag the alternative condition.

I set the 1st condition to compare the days open '>5' and if status didnt contain 'Approved', (true) , flag (red) is set, if False, go to next IF and compare days open '>0' and status contained 'Approved' (true) , flag (white) is set. I left the 2nd IF with an no false condition setting as i didnt need it since the other conditions were reflected in the first IF statement..

=IF(AND([Days Open Since Approval Request]@row > 5, [Approver BU Status (menu)]@row <> "Approved (as is)"), 1, IF(AND([Days Open Since Approval Request]@row > 0, [Approver BU Status (menu)]@row = "Approved (as is)"), 0))

Paul Newcome

1 is "true" meaning the flag is present. 0 is false meaning the flag is not there.

Rick Girard

yes, your right, i erred on my comment at the end there.. all good ! thanks. rg

Rick Girard

... well in reading through the logic (again), i learned something else new. the 'true' or 'false' condition can be placed in either the 1st or 2nd position where normally, '1' (true) is 1st position and '0' (false) is 2nd position (1, 0) , but in my case in the 2nd IF, I placed the 'true' condition as '0' in the 1st position rather than a '1', allowing me to keep the Flag white which is what i wanted.

Paul Newcome

Right. There are two sets of true/false that you are dealing with here. One true/false set is for the flag and the other is for the IF function. Where you place the 1 and 0 is going to depend on exactly how you write your IF. If you write the IF using the opposite logic, then you would swap the 1 and 0.