Welcome!

column colors change from two different column status

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

Find more posts tagged with

Comments

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.