2

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

Comments

Try:

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

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