RAG Formula & Date Formula

Cheryl Collins

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

Paul Newcome

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"))

Bassam Khalil

@Cheryl Collins

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

Paul Newcome

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"))

Cheryl Collins

Hi @Paul Newcome

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

Cheryl Collins

Hi @Bassam Khalil

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

Paul Newcome

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"))

Cheryl Collins

Hi @Paul Newcome

Bingo! Thats worked, thanks so much.

Cheryl

Paul Newcome

Happy to help. 👍️