Trying to get fancy with formulas - RYG with "AND" conditions

I'm trying to get a formula that will do the following:

Give me a RYG result in a column, by looking at whether a project item has gotten close to the due date, compared to what the status is on that item.

I want it to give a Red result if it is within a specific number of days of the due date and has less than 25% progress; Yellow for being within a certain number of days of the due date and less than 50% progress; and Green for being either outside of the Start Date (regardless of progress), or between the Start Date and Calendar Due Date and having over 50% progress.

Here's what I've been able to piece together so far from all the help links and formulas I've searched through.

=IF(AND(TODAY() - [Calendar Due Date]3 > 0, Progress3 < 0.25), "Red", IF(AND(TODAY() - [Calendar Due Date]3 > -3, Progress3 < 0.5), "Yellow", IF(AND(TODAY() - [Calendar Due Date]3 > -10, Progress3 < 1), "Green")))

 

Thanks in advance for any help y'all can provide!

Comments

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!