Need to Upgrade my Task Health Formula!

Hi! I have a very basic formula in place to calculate task health (red or green)

I am currently using:

=IF(AND(Status@row = <>"Complete", [Due Date]@row <= TODAY()), "Red", "Green")

I have two questions here.

1) I need this formula to also include "done" with "complete" IE... If the status is not "complete" OR "done" and the due date is less than today, task health = RED. (I have to use both DONE and COMPLETE, as my tasks that are synced to JIRA have a status of DONE **per JIRA** and all other tasks are marked as COMPLETE across all company projects (so I don't want to try and switch the wording to DONE for those).

2) Can I beef this formula up more? I would love for some tasks to populate YELLOW if the due date is in the next 7 days and the status is not "complete" or "done"

Thanks for your help!! 😀

Find more posts tagged with

Accepted answers

Paul Newcome

Give this a go...

=IF(OR(Status@row = "Complete", Status@row = "Done"), "Green", IF([Due Date]@row<= TODAY(), "Red", IF([Due Date]@row<= TODAY(7), "Yellow")))

All comments

Paul Newcome

Give this a go...

=IF(OR(Status@row = "Complete", Status@row = "Done"), "Green", IF([Due Date]@row<= TODAY(), "Red", IF([Due Date]@row<= TODAY(7), "Yellow")))

Hannah Gray

Thank you Paul! This worked for me.

Paul Newcome

Happy to help. 👍️

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.