How do I get the formula to work?

YH_2022

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"))))

JCluff

@YH_2022

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.

David Tutwiler

=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")))))

JCluff

@YH_2022

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.