Additional Formula Help Needed

Anthony D'Ambrosio

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"))))

Genevieve P.

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"))))

Genevieve P.

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

Anthony D'Ambrosio

@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.

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"))))

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

Genevieve P.

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