Formula to change status symbol if a task is not completed before due date

Sato757

Thanks guys, new to Smartsheet and need some help... Having issues with changing the status symbol to Red, Yellow, Green, or Gray based on project completion percentage column (%Complete) and start/due date columns ("Start Date" / "End Date"). I would like the following:

Gray - If %Complete equals to 0 and start date has not begun

Green - If %Complete is less than 1 and end date is more than 5 working days away

Yellow - If %Complete is less than 1 and end date is within 5 working days 

Red - If %Complete is less than 1 and end date has passed

 

Current formula (not working): 

=IF(AND([% Complete]2 = 0,[Start Date]2 < TODAY, "Gray"), IF(AND[% Complete]2 < 1,[End Date]2 < Today (5)), "Green", IF(And([% Complete]2 = 1, [End Date]2>Today(5), "Yellow", If(And[%Complete]2<Today(2), "Red")))

 

Thanks in advance!

Mike Wilday

It looks good except you forgot to close the yellow's AND statement. And were missing a closing bracket or two... Try this one. 

=IF(AND([% Complete]2 = 0,[Start Date]2 < TODAY, "Gray"), IF(AND[% Complete]2 < 1,[End Date]2 < Today (5)), "Green", IF(And([% Complete]2 = 1, [End Date]2>Today(5)), "Yellow", If([%Complete]2<Today(2), "Red"))))

Mike Wilday

Also, your red statement had an AND but only one criteria. I removed the AND statement.

Paul Newcome

I am also seeing some errors with parenthesis placement: TODAY function has none, and the "Gray" output is included in the AND statement instead of being set as the output to the first IF.

 

=IF(AND([% Complete]2 = 0,[Start Date]2 < TODAY()), "Gray", IF(AND[% Complete]2 < 1,[End Date]2 < Today (5)), "Green", IF(And([% Complete]2 = 1, [End Date]2>Today(5)), "Yellow", IF([%Complete]2<Today(2), "Red"))))

Mike Wilday

@Paul, Good catch! Yikes. 

Paul Newcome

THanks. Taking another look... I think there may be an issue with the "Red" criteria. Notice % Complete being compared to a date?

 

IF([%Complete]2<Today(2), "Red"

 

It may not necessarily be that the AND was not needed, but that the criteria was incorrect. But that's a call for the OP to make.

Sato757

Thanks Paul and Mike. Still getting some errors... tried to breaking it down to see where the disconnect is.

When I enter the formula:

=IF(AND([% Complete]2 = 0, [Start Date]2 < TODAY()), "Gray")

it works!

When I enter the following:

=IF(AND([% Complete]2 = 0,[Start Date]2 < TODAY()), "Gray", IF(AND[% Complete]2 < 1,[End Date]2 < Today (5)), "Green"))

it doesn't work...

Any ideas?

 

 

And yes, the % Complete should be < 1 and past the "end date" for it to be red.

Paul Newcome

I am also noticing that the Yellow criteria for % complete in the formula does not match the criteria spelled out in the post. and the Date criteria for Gray needs flipped. There is also nothing to differentiate working vs non-working days.

 

Here's an attempt at a full rewrite of the formula based on the written out criteria in the main body of the post...

 

=IF(AND([% Complete]@row = 0, [Start Date]@row > TODAY()), "Gray", IF([% Complete]@row < 1, IF([End Date]@row > TODAY(), IF(NETWORKDAYS([End Date]@row, TODAY()) > 5, "Green", "Yellow"), "Red"))

Paul Newcome

@Sato

 

In your above attempt, you are missing an opening parenthesis after the AND.

 

I posted a rewrite below. Try plugging that in (to include the @row references) and see if that works for you.

Sato757

@Paul I'm still having no luck. I think at this point, might have to call a coach or talk to the help desk. Really appreciate you helping me out. Thanks again! 

Paul Newcome

Happy to help. If you want to share a link to a published version of the sheet with sensitive/confidential data replaced with "dummy data", I may be able to get a better look at exactly how you have things working and what exactly you want to happen. It could be something as simple as a basic miscommunication.

Casey Edwards

I am trying to do something similar and having no luck. I need a formula that will auto-populate RYG if the status is not in the Completed status and Target End Date is more than 5 days away then populate with Green, if the status is not in the Completed status and Target End Date is greater than today then populate with Red, if the status is not in the Completed status and Target End Date is less than 5 days away but not today populate with Yellow. I cannot figure this out for some reason and it should be simpler. Anyone able to help?

Casey Edwards

@Paul Newcome You seem to be the Guru from a lot of threads I have seen so thought you may be able to help. Can you please see above comment? Thank you in advance.

Paul Newcome

@Casey Edwards Try something like this...

=IF(Status@row <> "Completed", IF([Target End Date]@row > TODAY(5), "Green", IF([Target End Date]@row > TODAY(), "Yellow", "Red")))