column colors change from two different column status

Cecelia Martin

I have a colored ball column that states if something is overdue from the due date to make the ball red. 

 

What I want is the ball to turn back green if the status changes to Completed.  I tried this

 

=IF(ISBLANK(Due3), "Gray", Status3 = "Complete", "Green", (IF(Due3 < TODAY(), "Red", IF(Due3 = TODAY(), "Yellow", IF(Due3 > TODAY(), "Green")))))

 

what am I doing wrong?  Everything is fine when I just use the due column but I can't quite firgure out the Status Complete changing it to green

 

 

Tim Meeks

Cecelia,

 

I'm getting close too.

 

I'm getting this work, =IF(Status9 = "Complete", "Green", IF(Due9 > TODAY(), "Green", IF(Due9 < TODAY(), "Red", IF(Due9 = TODAY(), "Yellow", "Gray"))))

 

However, if Due is blank, it defaults to Red.

 

I tried to Add (NOT(ISBLANK, but can't get it work....

 

=IF(OR(Status10 = "Complete", "Green", IF(NOT(ISBLANK((Due10 > TODAY(), "Green", IF(Due10 < TODAY(), "Red", IF(Due10 = TODAY(), "Yellow", "Gray")))))))

 

I think I'm close.... maybe someone else can take it a step further....

 

Mike Andreas

I'm finding this issue is a rather common one.  I answered a question with an end goal very similar to this one on another thread.

 

https://community.smartsheet.com/discussion/does-anyone-have-formula-syntax-control-ryg-and-gray-symbols-sheet

 

 You will end up with the completed ball being blue instead of green - hope that is ok.  In my mind, green is for when something is on track or not started, but of course I realize people have different definitions.

 

Also, it compares "Actual Percent Complete" to "Planned Percent Complete per Timeline", as opposed to a cell showing "Complete", as your request is.  It might be more than what what you're looking for, but I think a look is worth your time.