Multiple If/And Statements

edited 12/09/19

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

• ✭✭✭✭✭✭

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.

• No luck, thanks for the suggestion!

• ✭✭✭✭✭

=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

• ✭✭✭✭✭

This is my nested if maker

• Thank you so much!