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
-
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.
-
I found the error - it was my "<" that needed to point the other way for the date variation results!
Yeah, it works now!
-
Glad you got it figured out! I'm happy I could help!
-
Yes, testing each segment highlighted the error, then it was a quick fix
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 62 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!