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

  • Cheryl C
    Cheryl C
    edited 01/22/24 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

  • John_Foster
    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

  • 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
    John_Foster ✭✭✭✭✭✭
    edited 01/22/24

    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
    Cheryl C
    edited 01/22/24 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

  • John_Foster
    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!