Set Project RAG Symbol Based on End Date & % Complete

Cheryl C

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

Cheryl C

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

John_Foster

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

Cheryl C

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

John_Foster

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

Cheryl C

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

John_Foster

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