2 Conditions on 1 Status Indicator

Stevefc

I’m working on a project where I need to have to 2 conditions on 1 status indicator.

1 for tracking duration against today's date:

=IF(NETWORKDAY([Start Date]9 + 1, TODAY()) / Duration9 <= [% Complete]9, "Green", "Yellow")

2. Tracking if a task is not completed on time:

=IF(AND(TODAY() >= [End Date]10 + 1, [% Complete]10 < 1), "Red", "Green")

Is it possible to combine the 2 statements in 1 formula? Or should I be taking another approach?

Find more posts tagged with

Construction

Comments

L_123

try using another if statement

=if(Criteria, true, false) would go to

=if(criteria, if(criteria,true,false),false)

This way you can have multiple conditionals stacked inside each other.

Stevefc

Thanks for the update, however, my 2 IF statements have separate criteria and outcomes, would your suggest still work?

L_123

Yes, it will still work.

=if([Column1]1 <= 10, if([Column1]1 > 5, "Yellow","Red"),"Green")

The above will show when the value in the Column1 row 1 is:

0-5 = "Red"

6-10 = "Yellow"

11+ = "Green"

You can use this concept to even further extents, and stack as many if statements as you like. It can get rather complicated quickly though, so I would experiment with 2 if statements and see what results are given.

Stevefc

Thank you very much for your help so far, much appreciated.

I tried your solutions. If my first IF is true, I want a yellow status and for my second IF true a red status. IF both are false a green status. I’m just getting a red and green status no yellow, can you help?

=IF(NETWORKDAY([Start Date]3 + 1, TODAY()) / Duration3 >= [% Complete]3, IF(AND(TODAY() >= [End Date]3 + 1, [% Complete]3 < 1), "Yellow", "Red"), "Green")

L_123

What you have right now is

=if(criteria, if(criteria, "yellow","red"),"green")

So:

Criteria 1 | Criteria 2 | Result

True | True | Yellow

True | False | Red

False | X | Green

(the X means it isn't taken into account)

What you want is your second IF to be in the false category of the first.

=if(criteria, "yellow",if(criteria, "red","green"))

Criteria 1 | Criteria 2 | Result

True | X | Yellow

True | False | Red

False | True | Green

But there are many different ways to build this out. by switching the if statements you can use my earlier/your current format.

Your current formula is:

=IF(NETWORKDAY([Start Date]3 + 1, TODAY()) / Duration3 >= [% Complete]3, IF(AND(TODAY() >= [End Date]3 + 1, [% Complete]3 < 1), "Yellow", "Red"), "Green")

______________________________________________

You say you aren't getting yellow, so lets follow the path to where yellow should appear in your formula

If(Criteria,if(Criteria,"Yellow"

However, you are able to get to both false cases. This almost certainly means there is an issue with your second criteria.

AND(TODAY() >= [End Date]3 + 1, [% Complete]3 < 1)

Try to make this and statement pop true by editing the formula

Stevefc

Thanks for your quick response. I will check this out and let you know how I get on, thanks again.

Stevefc

I’ve now managed to get my status in the right order using the following,

=IF(AND(TODAY() >= [End Date]3 + 1, [% Complete]3 <= 1), "Red", IF(NETWORKDAY([Start Date]3 + 1, TODAY()) / Duration3 >= [% Complete]3, "Yellow", "Green"))

Thank you for all your help, much appreciated!

Sign in to join the conversation!

Quick Links

Trending discussions

Document filtered by form responses
I've created a Smartsheets form. When someone submits the form, I want them to automatically be emailed a document that has resources and links, but the document is filtered to only show the content that corresponds with what they selected in the form. Is something like this possible? What types of documents or automations…
Form stopped working after upgrade
I have a sheet that I created in a previous version, and it had a form. I was recently upgraded to a newer version and now my form is not working. I created a new form for the sheet, and it is giving me an error also. Does anyone know what I can do to fix this? The error says Form Unavailable. Sorry, the form you're trying…
Getting Error About Emulated Environments
Hi, I have read the post from 8/21/25. I am still getting the same error. I am setting up a new device and just download the latest version as of 9/5/25. I am using a Eunivon Smart White Board for touchscreen capability. It is running Android 14. Any help would be appreciated.