Multiple If/And Statements

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

Find more posts tagged with

Comments

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!

Sign in to join the conversation!

Quick Links

Trending discussions

Showing "stacked" data / Statuses of items by swimlane
I have multiple items that each have their own statuses and that roll up to "workstreams" or swimlanes. On the dashboard, I would like to show the progress of the items by swimlane without breaking it out into separate widgets for each. This would ideally be a stacked bar where each bar represented the Items and was broken…
How to Share Single Workspace Folder?
I have a workspace that I created with two subfolders. A user needs to have access to only one of them but when I have that folder selected, I can only share the entire workspace with them. Is there a secret way that I can share the parent folder and ensure that any new files created will be shared with the same people? I…
I have a Pro plan but I cannot create ANY reports
I have a Pro plan but I cannot create ANY reports. The system does not allow me to choose a folder, and consequently a sheet to create a row report and suggests an upgrade to Business or Enterprise. I'm quite new to Smartsheet and feel a bit disappointed with this limitation, or maybe I'm just doing something wrong. Alberto