Help with nested If formulat

Jawanza Hadley

These formulas work independently. I am attempting to combine them into one nested if formula.

1) if the %complete is full, status is green: =IF([% Complete]10 = "Full", "Green" , "Red")

2) if the finish date is greater thant today, status is green: =IF(Finish10 > TODAY(), "Green")

3) if %complete is full and finish date is less than today, status green: =IF(AND([% Complete]10 = "Full" , Finish10 < TODAY()) , "Green")

4) if finish date is greater than today and less than today +30 (within 30 days), status is yellow: else is red. =IF(AND(Finish10 > TODAY(), Finish10 < TODAY() + 30), "Yellow", "Red")

5) if % complete is empty, quarter, half, then red. if % complete is three quarter, then yellow. if % complete is full, green. =IF([% Complete]10 = "Empty", "Red", IF([% Complete]10 = "Quarter", "Red", IF([% Complete]10 = "Half", "Red", IF([% Complete]10 = "Three Quarter", "Yellow", IF([% Complete]10 = "Full", "Green")))))

I tried this and I get an unparseable error....

=IF([% Complete]10 = "Full" , "Green" , IF(Finish10 > TODAY(), "Green" , IF(AND([% Complete]10 = "Full" , Finish10 < TODAY()), "Green" , IF(AND(Finish10 > TODAY() , Finish10 < TODAY() + 30), "Yellow" , IF([% Complete]10 = "Empty" , "Red" , IF([% Complete]10 = "Quarter" , "Red" , IF([% Complete]10 = "Half" , "Red" , IF([% Completed]10 = "Three Quarter" , "Red" , IF([% Complete]10 = "Full" , "Green")))))))))

 

Kara Lumley

Here you go:

=IF([% Complete]10 = "Full" , "Green" , IF(Finish10 > TODAY(), "Green" , IF(AND([% Complete]10 = "Full" , Finish10 < TODAY()), "Green" , IF(AND(Finish10 > TODAY() , Finish10 < TODAY() + 30), "Yellow" , IF([% Complete]10 = "Empty" , "Red" , IF([% Complete]10 = "Quarter" , "Red" , IF([% Complete]10 = "Half" , "Red" , IF([% Complete]10 = "Three Quarter" , "Red" , IF([% Complete]10 = "Full" , "Green")))))))))

You had "completed" instead of "complete" in one argument

Best,

Kara

 

Jawanza Hadley

Thanks for your support Kara. I appreciate it. Small typo. So, I input the formula and tested the dates and the yellow doesn't work when the date is within 30 days. Is my logic off in terms of the order in which the nested "IF" statements will execute? Ideas? My goal is to no matter what, if the "Finish" date is within 30 days of being due, then change status to Yellow.

Kara Lumley

How about this - if the Status is not full, and the due date is within 30 days, the ball is yellow?

=(IF([% Complete]7 <> "Full", IF(AND(Finish7 > TODAY(), Finish7 < TODAY() + 30), "Yellow"), IF([% Complete]7 = "Full", "Green", IF(Finish7 > TODAY(), "Green", IF(AND([% Complete]7 = "Full", Finish7 < TODAY()), "Green", IF([% Complete]7 = "Empty", "Red", IF([% Complete]7 = "Quarter", "Red", IF([% Complete]7 = "Half", "Red", IF([% Complete]7 = "Three Quarter", "Red", IF([% Complete]7 = "Full", "Green"))))))))))

Jawanza Hadley

This works great for yellow status. But now, when Finish is outside of 30 days being due, the status column turns blank.

I am hoping to get this formula to work in the following order:

1) If finish > 30 days out, green status

2) if finish < TODAY() and % Complete = FULL then green

3) if finish < 30 days out, yellow status

4) Anything else is red

 

I may have written original formula incorrectly or too long.

Kara Lumley

Ok, try this:

=IF(AND(Finish23 < TODAY() + 30, [% Complete]23 = "Full"), "Green", IF(Finish23 > TODAY() + 30, "Green", IF(AND(Finish23 < TODAY() + 30, [% Complete]23 <> "Full"), "Yellow", "Red")))

Hope that helps!

Kara

Norman Bosell

You and Kara are very close!

 

I would suggest you change your criteria to the following:

1) If finish > 30 days out then green status

2) if % Complete = "FULL" then green status

(The date isn't needed to get what you want)

3) if and(finish >= today() and finish <= 30 days out then yellow status

(Using >= and <= in criteria 3 will eliminate a Red status on records where finish = today() and finish = today() + 30)

4) Anything else is red

 

Here's the formula I came up with:

=IF(Finish23 > TODAY() + 30, "Green", IF([% Complete]23 = "Full", "Green", IF(AND(Finish23> = TODAY(), Finish23 <= TODAY() + 30), "Yellow", "Red")))

 

Good Luck!

Norman

Jawanza Hadley

Thanks for your help Norman. Unfortunately I am still not seeing the behavior I am looking for. The status ball is green unless I change the % Complete to less than full. It then gives an error. However, I appreciate your logic in that it is requiring a formula in shorter length. Although at this point it isn't working quite yet. I am still stuck on this formula...!

J. Craig Williams

Here's my take on it:

=IF(OR(Finish23 > TODAY() + 30, [% Complete]23 = "Full"), "Green", IF(Finish23 > TODAY(), "Yellow", "Red"))

Craig

Jawanza Hadley

Thank you Craig! Your formula worked perfectly. I appreciate everyone who took the time to comment and provide suggestions!

Jeff Ford

This seems to be along the same lines as what I am trying to do except instead of status balls I wanted words.

I am trying to format the Status Column (See screenshot) so that based on what is in the CM Review Status Column or the Designer Response Column the Status Column Changes. 

I tried using multiple IF statements but I always get an unparseable error.

If one of you could help I would appreciate it, Thanks!