Additional Formula Help Needed

Hello Smartsheets Community!

I have another dilemma with one of the formulas some of you help me with. I was asked if there was a way to write the formula in the scheduled health column so that even at 0 % Complete that the schedule health ball changes to yellow when the end date is in range of the part of the formula that turns the ball yellow with out having to put a numeric value in the % Complete column.

For example, if you look at the first screen shot below. You can see that the End Date is scheduled for 10/29/21 and % Complete is at 0% and the ball color is grey. It only turns yellow as soon as you put a numeric value in the % complete column; second screen shot below.

Is this feasible and if so, how do I adjust my current formula:

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


Best Answer

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    Hi @Anthony D'Ambrosio

    Yes, that does make sense - I made the same error as the formula and stopped reading after a certain point!

    Lets put the RED statement first, then.

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

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

Answers

  • Hi @Anthony D'Ambrosio

    Your formula is correct, I would just change around the order! IF Statements will stop reading through the formula as soon as one of the statements meets the criteria.

    The first statement you have says that IF the % Complete is 0 but the date is in the future, turn gray. Have this be a statement after your yellow statement and then it will read this second, like so:

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


    Let me know if this resolved it for you!

    Cheers,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • Anthony D'Ambrosio
    Anthony D'Ambrosio ✭✭✭✭✭
    edited 10/29/21

    @Genevieve P.

    Thank you so much for the response. I took your formula and added into my project template. It did change the color ball yellow, but it stays yellow for dates that are now in the past when the color ball should be red. See screen shot below.

    Gray - if % Complete is 0 or 100

    Yellow - if the end date is within 3 days of that date

    Red - if the end date has past

    Does that help?



  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    Hi @Anthony D'Ambrosio

    Yes, that does make sense - I made the same error as the formula and stopped reading after a certain point!

    Lets put the RED statement first, then.

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

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • Anthony D'Ambrosio
    Anthony D'Ambrosio ✭✭✭✭✭

    @Genevieve P.

    That worked! Thank you so much and thank you for the quick response! I hope you have a great day and a wonderful weekend!

    Best,

    Anthony D'Ambrosio

  • I'm glad to hear it! I hope you have a wonderful weekend as well. 🙂

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!