Project Health Formula

Hey guys!

I am facing some difficulty to get a formula for the logic below:


Green

Actual Start Date is blank AND Today () < Target Start Date

Actual Start Date < Target Start Date AND status “in progress”

 

Yellow

Target End Date – Actual End Date >=0.1 * Duration planned AND Target End Date – End Date <=0.3 * Duration planned

  

Red

Actual Start Date is blank AND Today () > Target Start Date

Actual Start Date > Target Start Date

Today () > Actual Start Date + 1.3* Duration Planned


Please, could yo help me?

Answers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭

    Hi @Sara Pires

    Hope you are fine, please share me as an admin on a sample workspace contains your sheets and i will create the exact formula (after removing or replacing any sensitive information).

    My Email : Bassam.k@mobilproject.it

    PMP Certified

    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"

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Try this...


    =IF(OR(AND([Actual Start Date]@row = "", TODAY() < [Target Start Date]@row), AND([Actual Start Date]@row < [Target Start Date]@row, Status@row = "In Progress")), "Green", IF(AND([Target End Date]@row - [Actual End Date]@row >= 0.1 * [Duration Planned]@row, [Target End Date]@row - [Actual End Date]@row <= 0.3 * [Duration Planned]@row), "Yellow", IF(OR(AND([Actual Start Date]@row = "", TODAY() > [Target Start Date]@row), [Actual Start Date]@row > [Target Start Date]@row, TODAY() > [Actual Start Date]@row + (1.3 * [Duration Planned]@row)), "Red")))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!