How do I get the formula to work?
With the formula below, what do I add to make a blank end date cell black instead of red?
=IF(Status@row = "Complete", "Blue", IF(AND(Status@row = "Not Started", TODAY(5) <= [End Date]@row), UNICHAR(9899), IF(AND(Status@row <> "Complete", TODAY() > [End Date]@row), "Red", IF(AND(Status@row <> "Complete", TODAY(5) <= [End Date]@row), "Green", "Yellow"))))
Best Answer
-
You just need to add a ISBLANK() formula to the beginning of the formula.
=IF(ISBLANK([End Date]@row), UNICHAR(9899), IF(Status@row = "Complete", "Blue", IF(AND(Status@row = "Not Started", TODAY(5) <= [End Date]@row), UNICHAR(9899), IF(AND(Status@row <> "Complete", TODAY() > [End Date]@row), "Red", IF(AND(Status@row <> "Complete", TODAY(5) <= [End Date]@row), "Green", "Yellow")))))
This will provide:
Blank End Date = black circle
Complete = blue circle
Not Started & due in 6+ days = black circle
Not Complete & Past Due = red circle
Not Started or Complete & due in 6+ days = green circle ** I'd consider reviewing this logic **
Everything else = Yellow circle
Your green circle logic is almost identical to your other black circle. I'm not sure if this was intentional, but I wanted to point it out in case you wanted to review it.
Answers
-
=IF(ISBLANK([End Date]@row), "Black", IF(Status@row = "Complete", "Blue", IF(AND(Status@row = "Not Started", TODAY(5) <= [End Date]@row), UNICHAR(9899), IF(AND(Status@row <> "Complete", TODAY() > [End Date]@row), "Red", IF(AND(Status@row <> "Complete", TODAY(5) <= [End Date]@row), "Green", "Yellow")))))
-
You just need to add a ISBLANK() formula to the beginning of the formula.
=IF(ISBLANK([End Date]@row), UNICHAR(9899), IF(Status@row = "Complete", "Blue", IF(AND(Status@row = "Not Started", TODAY(5) <= [End Date]@row), UNICHAR(9899), IF(AND(Status@row <> "Complete", TODAY() > [End Date]@row), "Red", IF(AND(Status@row <> "Complete", TODAY(5) <= [End Date]@row), "Green", "Yellow")))))
This will provide:
Blank End Date = black circle
Complete = blue circle
Not Started & due in 6+ days = black circle
Not Complete & Past Due = red circle
Not Started or Complete & due in 6+ days = green circle ** I'd consider reviewing this logic **
Everything else = Yellow circle
Your green circle logic is almost identical to your other black circle. I'm not sure if this was intentional, but I wanted to point it out in case you wanted to review it.
