RAG Formula & Date Formula
Hi
I have 3 questions that I just cannot figure out!
1). I want to auto set my RAG based on a score - I've checked the other community questions and have a formula (shown below) based on previous answers but I'm getting a #UNPARESABLE error
2). I want to set a boolean based on whether a date is more than today +7 days in the past, but again, I'm getting a #UNPARESABLE error
3). I also need a formula that will tell me how many tasks are over due on a project plan based on End Date and Status (either NOT being "Completed" or "Cancelled", or BEING one of "Not Started", In Progress" or "Blocked")
TIA
Cheryl
Best Answer
-
Ugh. My apologies. My fingers were moving too fast. Commas. Not parenthesis.
For the final, you need to leave the "@cell" in place.
=COUNTIFS({02. iCompli Product Upgrade - Project Plan Range 2}, <TODAY(), {02. iCompli Product Upgrade - Project Plan Range 1}, AND(@cell <> "Completed", @cell <> "Cancelled"))
Answers
-
Hope you are fine, if you like me to fix the formula directly on your sheet please share me as an admin on a sample copy of your sheets ( Source & Destination ) and i will Create the exact formula for you then you can copy it to your original sheet.
My Email for sharing : Bassam.k@mobilproject.it
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
-
Your first two formulas have misplaced parenthesis.
In your first, take one of the two before "Red" and move it to just after "<= 4".
In the second, move the only comma there to after TODAY(-7).
The third formula will look something like this...
=COUNTIFS([End Date]:[End Date], @cell < TODAY(), Status:Status, AND(@cell <> "Completed", @cell <> "Cancelled"))
-
Thanks for coming back to me. The 2nd formula works so, thanks for this. The first and third are still being problematic:
1). I don't have a two parenthesis before "Red" so, I'm unsure what you mean, sorry. Also, if I put the parenthesis after "<=4" that will split the RAG that I want to apply to that so, again doesn't seem quite right to me?
2) My third formula is looking up the Plan sheet so, I have replaced the column names and @cell reference with the sheet look up reference:
=COUNTIFS({02. iCompli Product Upgrade - Project Plan Range 2}, <TODAY(), {02. iCompli Product Upgrade - Project Plan Range 1}, AND({02. iCompli Product Upgrade - Project Plan Range 1} <> "Completed", {02. iCompli Product Upgrade - Project Plan Range 1} <> "Cancelled"))
But, I'm getting a #invalidoperation
Thanks
Cheryl
-
Let me see what Paul comes back with, if that doesn't work, I will gladly share the sheet. The only problem is the 3rd formula is referring to a project plan sheet that I won't be able to share.
Thanks
Cheryl
-
Ugh. My apologies. My fingers were moving too fast. Commas. Not parenthesis.
For the final, you need to leave the "@cell" in place.
=COUNTIFS({02. iCompli Product Upgrade - Project Plan Range 2}, <TODAY(), {02. iCompli Product Upgrade - Project Plan Range 1}, AND(@cell <> "Completed", @cell <> "Cancelled"))
-
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!