Use a formula to change the row's health

Todd Michayluk

In a schedule, I'm trying to change the row's health to "red" when the current date is past the "Finish" date and the task is less then 100% complete. I can't get the formula to work when I try to make use of the "% Complete" column. Here is what I'm trying:

=IF(AND(Finish@row < TODAY(), [% Complete]@row <> "100"), "Red", "Green")

It accepts the formula, but the status is always Red no matter what value is entered in the "% Complete" cell.

Any thoughts?

Thanks,

Todd

Kelly Drake

If your Finish@row is blank that would be considered less than today's date so try adding in a segment that would account for that.

=IF(AND(Finish@row < TODAY(), NOT(ISBLANK(Finish@row)), [% Complete]@row <> "100"), "Red", "Green")

Paul Newcome

Also try replacing "100" with just 1 (no quotes). Smartsheet stores percentages as a part of ONE whole. So 100% = 1, 50% = 0.05, 25% = 0.25, so on and so forth.

Kelly Drake

IF(AND(Start@row> TODAY (), Start@row < TODAY (8)), "Yellow" .....

Kelly Drake

If your Finish@row is blank that would be considered less than today's date so try adding in a segment that would account for that.

=IF(AND(Finish@row < TODAY(), NOT(ISBLANK(Finish@row)), [% Complete]@row <> "100"), "Red", "Green")

Paul Newcome

Also try replacing "100" with just 1 (no quotes). Smartsheet stores percentages as a part of ONE whole. So 100% = 1, 50% = 0.05, 25% = 0.25, so on and so forth.

Todd Michayluk

@Paul Newcome and @Kelly Drake thanks so much for your help. Using 1 in place of 100% was what I was missing and I also liked the idea of accounting for if the Finish@row date was blank, so thanks to both of you for that.

I got rid of the Green status so the formula looks like this:

=IF(AND(Finish@row < TODAY(), NOT(ISBLANK(Finish@row)), [% Complete]@row < "1"), "Red", "")

Now let's step this up a bit. What would I need to add to this formula is I wanted to make the Health turn Green only if the Start@row date was within 1-5 days from today's date? Would this be possible?

Kelly Drake

@Todd Michayluk - You could nest another IF statement... but also you can remove the "" around the 1 in your current formula...

=IF(AND(Finish@row < TODAY(), NOT(ISBLANK(Finish@row)), [% Complete]@row < 1), "Red", IF(insert criteria that would make the row green here, "Green")

Todd Michayluk

https://community.smartsheet.com/discussion/comment/368283#Comment_368283

Thanks again for the help, I got it working how I wanted. Change Health to "Red" if the task is due within the next day, change Health to "Yellow" if the start date is within 1-7 days or change Health to "Green" if neither of these are met. Here is what it looks like:

Is there a cleaner/better way to enter the multiple Start@row values? Is there a way to do it with one entry and then use a range of 1-7?

Kelly Drake

IF(AND(Start@row> TODAY (), Start@row < TODAY (8)), "Yellow" .....

Todd Michayluk

https://community.smartsheet.com/discussion/comment/368342#Comment_368342

Ah so simple! Thanks again. Cheers!