Help with formula to change symbols based on status of task?

cjdoe

I'm developing a project management protocol for an educational intervention implementation. Want the task complete status to change color symbols based on:

Red = task is past end date (have end date column)

Yellow = task is 7 days from end date

Green = task is in progress between start date (have start date column) and 7 days prior to end date

Blue = task completion (have task completion column) checked

Blank if start date, end date, or task completion blank.

Pretty new to Smartsheet and would love help with this formula.

Thanks

Paul Newcome

@Mike TV There are a number of articles in the Help and Learning section as well as the Formula Handbook. There are actually a ton of resources outside of asking a question in the Community including help in various places regarding nested IF statements (which is what is needed in this case).

@cjdoe Give this a try:

=IF(AND([Start Date]@row <> "", [End Date]@row <> ""), IF([Task Completion]@row = 1, "Blue", IF([End Date]@row< TODAY(), "Red", IF([End Date]@row<= TODAY(7), "Yellow", IF([Start Date]@row<= TODAY(), "Green")))))

Mike TV

@cjdoe @Genevieve P.

I think SmartSheet either needs a department dedicated to helping people create their status formulas or a REALLY well built guide on how to set them up. There are just too many of these topics created weekly and it's nearly fruitless to help people set them up because 9 times of out 10 the person posting the topic doesn't provide enough criteria to set it up the way they truly want it. You have to go back and forth with the OP about 10x before it finally gets sorted out. I've noticed a lot of the veterans on this community forum have completely stopped responding to posts like these because they're so frequent and so time-consuming.

SmartSheet needs to come up with a better option to help these users set this up.

Paul Newcome

@Mike TV There are a number of articles in the Help and Learning section as well as the Formula Handbook. There are actually a ton of resources outside of asking a question in the Community including help in various places regarding nested IF statements (which is what is needed in this case).

@cjdoe Give this a try:

=IF(AND([Start Date]@row <> "", [End Date]@row <> ""), IF([Task Completion]@row = 1, "Blue", IF([End Date]@row< TODAY(), "Red", IF([End Date]@row<= TODAY(7), "Yellow", IF([Start Date]@row<= TODAY(), "Green")))))

cjdoe

@Paul Newcome that worked, thank you for your assistance.

Genevieve P.

Thanks for the tag, @Mike TV - you're right, there are a lot of questions around formulas in the Community which does indicate that they're not always intuitive for new users to implement. There is an ongoing project to constantly evaluate formulas as a whole to see how we can best help formula creators self-serve. For example, last year we released the Change Cell workflow, and Grouping and Summary in Reports which helped skip some steps.

In the meantime, as the product evolves, the Community is a fantastic resource to help troubleshoot formulas and I'm glad you joined us, @cjdoe!

@Paul Newcome and I also discussed the plethora of resources available in this other thread, if you're interested. 🙂

Paul Newcome

@cjdoe Happy to help. 👍️