Nested IF for symbols health status

Options

Good Day!

I am having an error message #UNPARSABLE. I am trying to create a formula to automatically change the health status project tasks. Our project management life cycle is very simple, we track only 6 milestones. Thus, I would like to compare all 6 milestones and provide the overall health status R-G-Y according to the rule below:


If the milestone delivery date is within the 10 coming days, the overall health is ''Red''

If the milestone delivery date is within the 11 and 20 days, the overall health status is ''Yellow''

If the milestone delivery date is greater that 21 days, the overall project status is ''Green''


IF(([Date Milestone1]@row<=[Date Milestone1]@row+10,"Rouge"; IF([Date Milestone1]@row>([Date Milestone1]+20);"Vert"; "Jaune"))......


Since I have 6 milestones, I think i need to add OR in the formula. Am I on the right track?


Date M1 or Date M2 or Date M3 or Date M4 or date M5 or Date M6 within 10 days; ''Red''

Date M1 or Date M2 or Date M3 or Date M4 or date M5 or Date M6 within greater than 20 days; ''Green''; ''Yellow''


Thanks,


Claude

Best Answer

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!