Welcome!

RGY Balls and Days remaining

Currently, I am using this formula

=IF([End Date]396 < TODAY(), "Red", IF([End Date]396 = TODAY(), "Yellow", IF([End Date]396 > TODAY(), "Green")))

for health status.

What I would like to do is tweak this, so that if a task is less than 75%, and due in 3 days, the ball is YELLOW. Everything else remains the same

Find more posts tagged with

Comments

Nic Larsen

Try:

=IF([End Date]@row< TODAY(), "Red", IF(AND([End Date]@row= TODAY(-3), [% Complete]@row < .75), "Yellow", IF([End Date]@row> TODAY(), "Green"

bcwilson.ca

Taking a bit of a guess with what you need..

If % complete is 100 % then GREEN
If it was due yesterday (or later and is not done) the RED
If it is due between today and 3 days from now and under 75% it is YELLOW.
If it is due today to 3 days from now and equal to or over 75% it is GREEN
If it is due past 3 days from now it is green

=IF([% Complete]@row = 1, "Green", IF([End Date]@row < TODAY(), "Red", IF(AND([End Date]@row < TODAY(3), [% Complete]@row < 0.75), "Yellow", "Green")))

In your scenario even if the task is done it will show red as soon as it falls in the past
Do you want tasks that are not done in the future showing green or just blank?
- I would probably have it show nothing and green within the 3 days

Below will accomplish that

=IF([% Complete]@row = 1, "Green", IF([End Date]@row < Today(), "Red", IF(AND([End Date]@row < TODAY(3), [% Complete]@row < .75), "Yellow", IF([End Date]@row < TODAY(3), "Green", ""))))

If % complete is 100 % then GREEN
If it was due yesterday (or later and is not done) the RED
If it is due between today and 3 days from now and under 75% it is YELLOW.
If it is due today to 3 days from now and equal to or over 75% it is GREEN
If it is due past 3 days from now it is BLANK

Sign in to join the conversation!

Quick Links

Trending discussions

User Types for Update Requests/Forms
Hi there! Our Account Manager today informed us that users would need to be paid members to receive and complete Update Requests going forward. I can't imagine that Form users would need to be members, but I don't know how that differs from Update Requests, since neither requires access to the underlying sheet. Is it…
Generating documents from row data
This discussion was created from comments split from: Generate documents from row data.
Getting error - need help!
Error occurs when opening Vault Apps in Smartsheet WorkApps. The app fails to load and displays an error message despite logging out and back in. Issue has been ongoing since last Monday