Health Status -multiple criteria and blanks

✭✭

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

• ✭✭✭✭✭✭

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!

Thanks,

Kaveri Vipat

Senior Associate - Smartsheet Development, Ignatiuz Software

2023 Core Product Certified

Did this answer help you? Show some love by marking this answer as "Insightful💡" or "Awesome❤️" and "Vote Up⬆️"

• ✭✭✭✭✭✭

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!

Thanks,

Kaveri Vipat

Senior Associate - Smartsheet Development, Ignatiuz Software

2023 Core Product Certified

Did this answer help you? Show some love by marking this answer as "Insightful💡" or "Awesome❤️" and "Vote Up⬆️"

• ✭✭

This worked perfectly, thank you so much!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!