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?

Find more posts tagged with

Support

Project Management

Accepted answers

All comments

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

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.