Set Project RAG Symbol Based on End Date & % Complete
Hi
I want to set a Project RAG symbol based on the proximity of the End Date and the % complete.
Ideally the following:
If End Date TODAY() + 10 and % Complete >"25%" = Green
If End Date TODAY() + 5 and % Complete <="50%" = Amber
If End Date TODAY() + 3 and % Complete <"75%" = Green
TIA
Cheryl
Best Answer
-
Hi @John_Foster
I've cracked it using the following:
=IF(AND([End Date]@row > TODAY(10), [% Complete]@row > 0.25), "Green", IF(AND([End Date]@row > TODAY(5), [% Complete]@row <= 0.5), "Yellow", IF(AND([End Date]@row < TODAY(3), [% Complete]@row < 0.75), "Red", "")))
Thanks for your help
Cheryl
Answers
-
Hi @Cheryl C,
I believe that this is the formula you want.
=IF(AND([End Date] < TODAY(10),[% Complete] 0.25),"Green",IF(AND([End Date] < TODAY(5),[% Complete]<=0.5),"Amber",IF(AND([End Date] < TODAY(3),[% Complete]<0.75),"Green","")))
I have assumed that the end date for each should be greater than the today + ?? values.
I think that the check you may have wanted to be red, but I have used green as per your original request, I have also left the final value blank, if you want it to be a specific color when it matches none of the other options, please change the last section between the quotes.
John
-
Hi @John_Foster
Thanks for this, it's definitely got me on the right track however, something isn't quite right. This is the formula I have entered:
=IF(AND([End Date]@row < TODAY(10), [% Complete]@row > 0.25), "Green", IF(AND([End Date]@row < TODAY(5), [% Complete]@row <= 0.5), "Yellow", IF(AND([End Date]@row < TODAY(3), [% Complete]@row < 0.75), "Red", "")))
When I test it on row 3 - you can see the date is tomorrow but less than 75% complete but it's showing green as the RAG when it should be Red
-
Hi @Cheryl C,
It is being caught on the first part of your formula.
The end date is less than today + 10 days, please reverse the signage to > and this should work. You may need to do the same for the other two IFs as well.
Sorry about the error in my original post.
John
-
Hi @John_Foster
I've cracked it using the following:
=IF(AND([End Date]@row > TODAY(10), [% Complete]@row > 0.25), "Green", IF(AND([End Date]@row > TODAY(5), [% Complete]@row <= 0.5), "Yellow", IF(AND([End Date]@row < TODAY(3), [% Complete]@row < 0.75), "Red", "")))
Thanks for your help
Cheryl
-
Hi @Cheryl C
That's great, I am pleased I was able to help.
Please mark one of the answers as such to allow others who may come across this post to be pointed in the right direction.
Thanks,
John
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.1K Get Help
- 430 Global Discussions
- 149 Industry Talk
- 488 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 153 Just for fun
- 74 Community Job Board
- 499 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!