Health Status -multiple criteria and blanks

rlowry

Hi, I have searched the forum and tried multiple formulas, but I'm not having any luck. Can you please help me?

I have a "Due Date" column and "Status" column, and need the Health Column (red, blue, yellow, green) to show this:

Green = Completed

Blue = Not Started (and due date greater than 14 days away)

Blue = Due Date blank

Red = Not Started and due date less than 14 days away

Red = Due Date past due and not Complete

Yellow = everything else

Everything is working using the formula below except the Blue for blank Due Date. Any guidance would be greatly appreciated!

=IF(Status1 = "Complete", "Green", IF(AND(Status1 <> "Complete", TODAY() > [Due Date]1), "Red", IF(AND(Status1 = "Not Started", TODAY() + 14 > [Due Date]1), "Red", IF(AND(Status1 = "Not Started"), "Blue", IF(AND([Due Date]1 = ""), "Blue", "Yellow")))))

Kaveri Vipat

Hi rlowry 

I hope you are doing well and safe, 

Please try this formula - =IF(Status@row = "Completed", "Green", IF(OR(AND(Status@row = "Not Started", [Due Date]@row > TODAY() + 14), [Due Date]@row = ""), "Blue", IF(OR(AND(Status@row = "Not Started", [Due Date]@row < TODAY() + 14), AND([Due Date]@row < TODAY(), Status@row = "not Completed")), "Red", "Yellow"))) 

I hope this is useful to you, please let me know if you need any other help.  

Have a Good Day! 

Kaveri Vipat

Hi rlowry 

I hope you are doing well and safe, 

Please try this formula - =IF(Status@row = "Completed", "Green", IF(OR(AND(Status@row = "Not Started", [Due Date]@row > TODAY() + 14), [Due Date]@row = ""), "Blue", IF(OR(AND(Status@row = "Not Started", [Due Date]@row < TODAY() + 14), AND([Due Date]@row < TODAY(), Status@row = "not Completed")), "Red", "Yellow"))) 

I hope this is useful to you, please let me know if you need any other help.  

Have a Good Day! 

rlowry

This worked perfectly, thank you so much!