Status light, nested IF statement


Hello everyone,

I'm looking to find some help with my status light formula.

My columns are as follows:

• Date Due (date column)

• Progress (drop down column with: "Not Started", "In Progress", "Complete" as options)

• Status (drop down symbol column with "Red", "Yellow", Green", "Gray" circles -- to become a formula column hopefully)

Here is the logic:

• If status@row is "Not Started", OR "In Progress", OR is blank, AND today is greater than date due , show "Red" (ie. if today is past the due date and NOT complete)

• If status@row is "complete" and any value for date due OR is blank , show "Green" (ie. if it's complete, no matter the date)

• If status@row is "In Progress" and date due is greater than today OR is blank, show "Yellow" (ie. if it's in progress and the date due has not occurred)

• If status@row is "Not Started" OR is blank and date is greater than today OR is blank, show "Gray" (ie. if no progress has been made yet and the date due has not occurred or has not been assigned)

As seen below, here is the formula I have so far:

=IF(Progress@row = "Complete", "Green", IF(AND(Progress@row = "In Progress", ISBLANK([Date Due]@row)), "Yellow", IF(AND(Progress@row = "Not Started", OR([Date Due]@row > TODAY(), ISBLANK([Date Due]@row))), "Gray")))

I've tried a few different things and this one in it's current state (at the very bottom) just appears blank. I suspect I have not captured the logic correctly or in the right order.

Any help would be so great!

Thanks :)



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!