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

Digital_Kru

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!

Mike Wilday

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. 

 

Digital_Kru

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

Yeah, it works now!

Mike Wilday

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

Digital_Kru

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