RAG Status formula scenario

Avinash Chandra
Avinash Chandra ✭✭✭✭✭
edited 03/29/23 in Formulas and Functions

Hi Smartsheets wizards,

Good Evening!

I have created a formula that has been working great, however I find that if the actual finish date is missing for any reason the RAG does not change to RED. Here is the formula and the screenshot of the schedule. Kindly advise what I should change to get the RAG to change to red if the actual date is missing for any reason. Look at the date 8th Feb 2023 row.


=IF(OR(AND([Trending Start]@row < TODAY(), [Actual Start]@row = ""), AND(([Actual Start]@row - [Trending Start]@row) >= 20, [% Complete]@row <= 0.1)), "Red", IF(OR(AND(([Actual Start]@row - [Trending Start]@row) >= 10, [% Complete]@row < 0.5), AND([Actual Finish]@row > [Trending Finish]@row, [% Complete]@row <= 0.5)), "Yellow", IF(AND([% Complete]@row = 1, [Actual Finish]@row <> ""), "Blue", "Green")))

Answers

  • Julie Fortney
    Julie Fortney Overachievers

    Hi @Avinash Chandra,

    Here's what I've interpreted from your formula:

    Red

    trending start is in the past and there is no actual start date

    less than 10% complete and actual start is greater than or equal to 20 days after trending start

    Yellow

    less than 50% complete and actual start is great than or equal to 10 days after trending start

    less than 50% complete and actual finish is after trending finish

    Blue

    task is complete and actual finish is not blank

    Otherwise Green

    Could you confirm that you in fact want the health to be red in any row that is missing an Actual Finish date? If so, you could add that to the beginning of your formula.

    www.linkedin.com/in/julie-fortney-pmp-lssblackbelt

  • Avinash Chandra
    Avinash Chandra ✭✭✭✭✭
    edited 03/30/23

    What is happening: People are copying and pasting Trending start date into actual start date and missing out filling actual finish date. RAG remains Green. It should change to RED.

    How do I add double condition to this: (([Actual Start]@row - [Trending Start]@row) >= 20, [% Complete]@row <= 0.1)), "Red", AND Actual finish date is blank.


    Kindly advise.

  • Julie Fortney
    Julie Fortney Overachievers

    Based on the formula you posted above, you want to add the condition as shown in bold italics below, correct?


    Red

    trending start is in the past and there is no actual start date

    less than or equal to 10% complete, actual start is greater than or equal to 20 days after trending start, and Actual Finish is blank

    Yellow

    less than 50% complete and actual start is great than or equal to 10 days after trending start

    less than 50% complete and actual finish is after trending finish

    Blue

    task is complete and actual finish is not blank

    Otherwise Green

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!