Use a formula to change the row's health
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
Best Answers
-
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")
Kelly Drake (she/her/hers)
STARBUCKS COFFEE COMPANY| business optimization product manager
-
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.
-
IF(AND(Start@row> TODAY (), Start@row < TODAY (8)), "Yellow" .....
Kelly Drake (she/her/hers)
STARBUCKS COFFEE COMPANY| business optimization product manager
Answers
-
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")
Kelly Drake (she/her/hers)
STARBUCKS COFFEE COMPANY| business optimization product manager
-
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.
-
@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?
-
@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")
Kelly Drake (she/her/hers)
STARBUCKS COFFEE COMPANY| business optimization product manager
-
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?
-
IF(AND(Start@row> TODAY (), Start@row < TODAY (8)), "Yellow" .....
Kelly Drake (she/her/hers)
STARBUCKS COFFEE COMPANY| business optimization product manager
-
Ah so simple! Thanks again. Cheers!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.7K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!