Formula for project health

I have the following formula and I want to add:

Turn task health Blue for START DATE column if today's date is before the start date.

Leave Task Health blank if there is not Start Date or Target End Date

=IF(OR(AND([Target End Date]@row <= TODAY(7), [% Complete]@row < 0.5), AND([Target End Date]@row < TODAY(), [% Complete]@row < 1)), "Red", IF(OR([% Complete]@row = 1, [Target End Date]@row >= TODAY(7)), "Green", IF(AND([Target End Date]@row <= TODAY(7), [% Complete]@row >= 0.5), "Yellow")))

Find more posts tagged with

Accepted answers

Nick Korna

You'd just add these bits at the start to get:

=IF(OR(ISBLANK([Start Date]@row), ISBLANK([Target End Date]@row)), "", IF([Start Date]@row > TODAY(), "Blue", IF(OR(AND([Target End Date]@row <= TODAY(7), [% Complete]@row < 0.5), AND([Target End Date]@row < TODAY(), [% Complete]@row < 1)), "Red", IF(OR([% Complete]@row = 1, [Target End Date]@row >= TODAY(7)), "Green", IF(AND([Target End Date]@row <= TODAY(7), [% Complete]@row >= 0.5), "Yellow")))))

Hope this helps!

All comments

Ipshita

Hi @Melissa F 13 -

I tested out your problem and here is the solution. Use the formula below, it would turn the task health blue where date is greater then today and leave it blank if the start date is blank and you can add the logic for ned date the same way. Hope this helps!

Cheers,

Ipshita

Melissa F 13

Can you provide this added into the formula I provided above?

Nick Korna

You'd just add these bits at the start to get:

Hope this helps!

Melissa F 13

Fantastic, thank you!

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.