If / Then using the red, yellow, green

Tammy Luther · 10/05/20

I am new to Smartsheets, so this may be a basic knowledge question ...

I'm trying to use the red, yellow, green, (really just red and green) for the following scenario ...

Start date is 10/1/20, due date is 10/6/20, another column for completed date.

Using the completed date if it is <due date, then green, if it is finish date, then red. Here is what I have . The green works, but I can't get the red to display.

=IF(Completed7 < Due7, "Green"), IF(Completed7 > Due7, "Red")

Thank you in advance !

M Underbrink · 10/05/20

When you are using nested IF statements, you can't close out the brackets on the first IF statement.

=IF(Completed7 < Due7, "Green", IF(Completed7 > Due7, "Red"))

By moving your closing parenthesis to the end, you are allowing the first IF statement to evaluate the second IF statement as the "False" condition. Let me know if it works!

Andrée Starå · 10/12/20

Hi @Tammy Luther

Try this.

Edit to include both columns.

=IF(OR(Due@row = "", Completed@row = ""), "", IF(Completed@row < Due@row, "Green", IF(Completed@row > Due@row, "Red")))

Did that work?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Andrée Starå · 10/12/20

@Tammy Luther

Excellent!

You're more than welcome!

✅Remember! Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

M Underbrink · 10/05/20

When you are using nested IF statements, you can't close out the brackets on the first IF statement.

=IF(Completed7 < Due7, "Green", IF(Completed7 > Due7, "Red"))

By moving your closing parenthesis to the end, you are allowing the first IF statement to evaluate the second IF statement as the "False" condition. Let me know if it works!

Tammy Luther · 10/05/20

Thank you !! I am somewhat new to writing if then statements and figured it was the parenthesis, but just could not get it figured out. Looks to be working. 😀

M Underbrink · 10/05/20

Awesome! Glad it's working. 😊

Tammy Luther · 10/05/20

The green / red status symbol displays based on the date entered, but now as I start to populate, it shows green on all columns even through I don't have any data in the completed column yet. When I display the sheet, it kind of is misleading as it shows future dates as green. Is there a way I can put in there to only use the status if there is data in the completed column?

Does this make sense?

Tammy Luther · 10/12/20

Hey Mark,

I'm still new to this chat thing and it seems like I may be missing something here ... I wanted to see if there was a way to not show any status (red/green) if there is no date entered into the column it is pulling data for the if/then. Do you have any thoughts?

Andrée Starå · 10/12/20