RYG Status

Darcy Poole
Darcy Poole ✭✭
edited 12/09/19 in Formulas and Functions

I've looked through numerous posts and sent an email to Smartsheet but am still on the hunt for the exact answer. (I do have a HelpDesk ticket in.) 

I'm creating a sheet that will be used to conduct document audits and I need to know when an audit is 

1. Previous to due date

2. Due 

3. Overdue

4. Completed (This is the one I'm struggling with. I can't get it to change color after completion.) 

This is the formula I'm currently using:

=IF([Due Date]1 < TODAY(), "Red", IF([Due Date]1 = TODAY(), "Yellow", IF([Due Date]1 > TODAY(), "Green")))

Thank you!

Darcy Poole | Operations System Specialist



  • Nic Larsen
    Nic Larsen ✭✭✭✭✭✭

    I'm guessing you have a column or a check box to say completed? Something like this? Start with this criteria so it'll ignore the dates if it truly is completed. 

    =IF([Insert Column Name])@row = "Completed", "Gray", IF([Due Date]@row< TODAY(), "Red", IF([Due Date]@row= TODAY(), "Yellow", IF([Due Date]@row> TODAY(), "Green")))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!