Multiple If/And Statements

Katie Cobb

I am trying to work through multiple if/and statements to use red/yellow/green/gray indicators without much luck.  Essentially, I would like the formula to produce the following:

If % Complete = 1, Green

If % Complete < 1, and finish date is within 7 days, Red

If % Complete < 1, and finish date is within 14 days, Yellow

If % Complete < 1, and finish date is over 15 days, Gray

 

Any help would be appreciated!

Thank you

L_123

If([% Complete]@row = 1, "Green", If(today()-[finish date]@row<= 7, "Red",If(today()-[finish date]@row  <= 14, "Yellow","Gray"

 

Untested. Give that a try and see if it works for you.

Katie Cobb

No luck, thanks for the suggestion!

bcwilson.ca

=IF([% Complete]@row = 1, "Green", IF([Finish Date]@row - TODAY() <= 7, "Red", IF([Finish Date]@row - TODAY() <= 14, "Yellow", "Gray")))

You don't need an "AND" as you have already tested for 100% completion.



In other words your logical expression tests "completion = 1" so as a result, everything else is not complete.. you don't need to retest for that...

IF(logical_expression, value_if_true, value_if_false) 

So you really just need to check the date to determine "how not done it is"...

And since everything 15 or over is gray that is another logic case you do not have to test for... It is the last false case

Hopefully, that makes sense

bcwilson.ca

This is my nested if maker

Katie Cobb

Thank you so much!