How to combine these?

bobloblaw

Need the formula to say:

• If Completed - Green
• If Canceled - Gray
• If In Progress - Yellow
• If Not Started - Red
• If On Hold - Yellow
• If In Progress, Not Started, or On Hold and and today is greater than End@row - Red

Here is what I have.... Works fine. Now need to add in the last bullet and I'm stumped.

=IF(Status@row = "Completed", "Green", IF(Status@row = "Canceled", "Gray", IF(Status@row = "In Progress", "Yellow", IF(Status@row = "Not Started", "Red", IF(Status@row = "On Hold", "Yellow"

Gia Thinh

Hi bobloblaw,

I suggest to add End@row checking into your formula with AND function as below:

=IF(Status@row = "Completed", "Green", IF(Status@row = "Canceled", "Gray", IF(AND(Status@row = "In Progress", End@row>=TODAY()), "Yellow", IF(AND(Status@row = "Not Started", End@row>=TODAY()), "Red", IF(AND(Status@row = "On Hold", End@row>=TODAY()), "Yellow", IF(AND(OR(Status@row = "In Progress", Status@row = "Not Started", Status@row = "On Hold" ), End@row<TODAY()),"Red")))

Try it!