Project Health Formula

Sara Pires

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?

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 : [email protected]

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")))