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

Tags:
· Share on FacebookShare on Twitter

Comments

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

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!