Formula Question/ Help Needed

I currently have the following Formula updating a STATUS (Not Started, Complete, IN Progress) based on % Completed. Works flawlessly

=IF([% Complete]272 = 1, "Complete", IF([% Complete]272 >= 0.01, "In Progress", IF([% Complete]272 = 0, "Not Started"))

I want to take this to another level. As I have another column "Health" using symbols (Green, Yellow, Red, Blue) each identifying a tasks progress (Green = completed, Blue - In progress/On time, Yellow - 3 days before due/not 100%, and Red = missed due date/ not 100%

=IF([% Complete]492 = 1, "Blue", IF(TODAY() - Finish492 > -1, "Red", IF(TODAY() - Finish492 > -3, "Yellow", "Green")))

I want to take the FIRST formula to the next level, by having it recognize when a task is RED or Past Due and so it would flip the STATUS to, Past Due. And this is where I'm stuck!

So it would be this working STATUS working formula + referencing the HEALTH column, when RED.

=IF([% Complete]272 = 1, "Complete", IF([% Complete]272 >= 0.01, "In Progress", IF([% Complete]272 = 0, "Not Started"))

My failed Attempt.................

=IF([% Complete]272 = 1, "Complete", IF([% Complete]272 >= 0.01, "In Progress", IF([% Complete]272 = 0, "Not Started", IF Health01 = RED, "Past Due"))

Problem is, this doesn't work.... :(

Tags:

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!