IF AND formula for RGY

mmurphy14986

I am working on a project plan that needs to show the "health" of each task based on the finish date and the status.  I've been able to do a formula that checks the status and then selects the correct RGY and I've been able to do a formula that checks the finish and selects the correct RGY but I've been unsuccessful with combining the two in a nested formula.  Here is what I've tried so far:

=IF(TODAY() > Finish6, IF(Status6 = "Not Started", "Red", IF(Status6 = "Completed", "Green", IF(Status6 = "In Progress", "Yellow"))))

 

Mike Wilday

And statements have a specific arragnement See here for more help... https://help.smartsheet.com/function/and

Try this function:

=If(AND(TODAY() > Finish6, Status6 = "Not Started"), "Red", IF(Status6 = "Completed", "Green", IF(Status6 = "In Progress", "Yellow")))

 

mmurphy14986

I tried the above formula and it returned a blank.  Do I need to add the AND statement to the rest of the formula after each IF?  Meaning:

=If(AND(TODAY() > Finish6, Status6 = "Not Started"), "Red", IF(AND(Status6 = "Completed", "Green", IF(AND(Status6 = "In Progress", "Yellow")))

I added a screenshot so you can see what the formula returned and what I'm trying to do.

Thanks for your help...

Robert S.

Hello,

 

Thanks for the question. Both the formula you originally posted and the one that Mike posted will work for specific use cases, however they will have slightly different outcomes depending on the combination of the "Status" and "Finish" columns. You're original formula works like this:

• If the Finish date is in the past and Status is set to...
  • Not Started, the ball will be Red
  • In Progress, the ball will be Yellow
  • Completed, the ball will be Green
• If the Finish date is today or in the future the cell remains blank regardless of what the Status is set to

Mike's formula works like this:

• If the Finish date is in the past and Status is set to...
  • Not Started, the ball will be Red
  • In Progress, the ball will be Yellow
  • Completed, the ball will be Green
• If the Finish date is today or in the future and Status is set to...
  • Not Started, the cell will remain blank
  • In Progress, the ball will be Yellow
  • Completed, the ball will be Green

Mike's formula doesn't take the Finish date into consideration if the Status is set to In Progress or Completed. Not to say that either one of these formulas is incorrect however, as one of these may return the intended result. If so then putting them into the "Health" column should result as I've listed above. If neither of these results are what you're looking for, please provide a similar breakout of the results you'd expect from this formula and I'll be happy to help with creating a formula to meet those results as soon as I can.

 

You mentioned you're getting a blank in a row that should be resulting in what looks to be Green when using Mike's formula. This may be due to the row you've placed the formula in being a different row than the row referenced in the formula. If you copied the formula from Mike's response and pasted it into any row other than row 6 this is likely the issue. You can resolve this by either moving the formula to row 6 or changing the formula to reference the row you've placed it in.

Mike Wilday

Here is a slight modification that will flag red if the Finish date is in the past and the task is not started or in progress. Good catch Robert! And yes, you need to change all the 6's to whatever row you are putting the formula in.  

=If(OR(AND(TODAY() > Finish6, Status6 = "Not Started"),And(Today() > Finish6, Status6 = "In Progress")) "Red", IF(Status6 = "Completed", "Green", IF(Status6 = "In Progress", "Yellow")))