Stoplight formula help
Hi all...
I've gotten some good help on this forum before, so hopefully you all can help me out.
I'm trying to create a stoplight formula for tasks in a project plan. I would like to trigger colors of red, yellow, green based on the percentage complete of that task, and how far we are from the expected finish date of that task (column titled "Baseline Finish"). Someone here suggested I create a checkbox column that flags when today's date is within 7 days of baseline finish, which along with percentage complete would trigger the stoplight color. The formula we landed on is:
=IF(AND([7 Day Flag]@row = true, [% Complete]@row <= 0.25), "Red", IF(AND([7 Day Flag]@row = true, [% Complete]@row >= 0.75), "Green", IF(AND([7 Day Flag]@row = true, [% Complete]@row > 0.25, [% Complete]@row < 0.75), "Yellow", IF(AND[7 Day Flag]@row = false, [% Complete]@row <= 0.75), "Yellow", IF(AND([7 Day Flag]@row = false, [% Complete]@row > 0.75), "Green"))))
It was working fine until I added the "flag = false" functions, and now it is unparseable.
Does anything jump out as wrong? I'd appreciate any help. Thanks!
Answers
-
Hello @schletpe
Instead of using True / False, have you tried using 1 and 0 ?
IF([This column]1=1,"true","false") will post true when the checkbox is checked.
IF([This column]1=0,"true","false") will post true when the checkbox is unchecked.
Hope this helps!
-
For the formula you mentioned above, you are missing "(" in the highlighted section.
To cut out the helper column, use the below formula:
=IF(AND([Baseline Finish]@row - TODAY() <= 0, [% Complete]@row <> 1), "Red", IF(AND([Baseline Finish]@row - TODAY() <= 7, [% Complete]@row <= 0.25), "Red", IF(AND([Baseline Finish]@row - TODAY() <= 7, [% Complete]@row > 0.25, [% Complete]@row < 0.75), "Yellow", IF(AND([Baseline Finish]@row - TODAY() <= 7, [% Complete]@row >= 0.75), "Green", IF(AND([Baseline Finish]@row - TODAY() > 7, [% Complete]@row <= 0.75), "Yellow", IF(AND([Baseline Finish]@row - TODAY() > 7, [% Complete]@row > 0.75), "Green", "Green"))))))
edit - built in additional logic to show green symbol if % Complete was 100% and baseline was 0 or less.
-
Thank you! Adding the missing ")" worked.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!