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

Options
edited 12/09/19

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!

• ✭✭✭✭✭✭
Options

I would break down each and statement and make sure you're getting the results you want. At a quick glance, it looks correct. but I would make sure that =Today()-Calendar Due Date]3 works and what your answer is. Sometimes I get positive results when I am expecting negative ones. You can also flip-flop those and subtract today from the due date.

Otherwise, it looks like it should work fine for you.

• Options

I found the error - it was my "<" that needed to point the other way for the date variation results!

Yeah, it works now!

• ✭✭✭✭✭✭
Options

Glad you got it figured out! I'm happy I could help!

• Options

Yes, testing each segment highlighted the error, then it was a quick fix

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!