RYG Automation using IF/AND function

SandyAbegail

Hi,

I am creating a project management sheet with RYG symbols to trcak our progress.  

I have a sheet with the projects, status and due dates. What I'm trying to achieve is for the RYG symbols to automate based on the status and deadlines.

If status is either in progress or for review, and is 2 days before the due date, should be yellow, if due date is today or more, then red, else (3 ore more days before due) should be green. 

I've been trying IF/AND functions but I can't get it right. 

Please help. 

Paul Newcome

Try something like this...

 

=IF([Due Date]@row >= TODAY(3), "Green", IF([Due Date]@row =< TODAY(), "Red", IF(AND(OR(Status@row = "In Progress", Status@row = "For Review"), [Due Date]@row = TODAY(2)), "Yellow")))

SandyAbegail

Thanks Paul. 

 

Initially, I got an INVALID OPERATION error, I just have to switch the = and < signs; the correct order being <= to make the formula.

 

 

Paul Newcome

Happy to help! 

 

And you are correct. Smartsheet is very particular on the order of the operators. My fingers and brain just weren't moving at the same speed apparently. Haha.