Project Health Formula

Options

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

• ✭✭✭✭✭✭
Options

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

bassam.khalil2009@gmail.com

• ✭✭✭✭✭✭
Options

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!