Health Status Formula dependent on due date

I am experiencing trouble trying to write a formula for the below situation. See above screenshot as reference. Can someone please advise on how to write this formula correctly?

This is the current formula I'm using, but I know it's not correct:

=IF[On Hold] = 1, "Gray", IF([Due Date]@row >= TODAY(15), "Green", IF(AND([Due Date]@row >= TODAY(3), [Due Date]1 <= TODAY(14)), "Yellow", IF([Due Date]@row < TODAY(3), "Red")))

This is the situation I want to solve for:

If the DUE DATE is within 3 days of TODAY or in the past, turn the health symbol "Red"
If the DUE DATE is within 3 to 14 days from today, turn the health symbol "Yellow"
If the DUE DATE is 15 or more days from today, turn the health symbol "Green"
If the ON HOLD flag is checked, turn the health symbol "Gray"
Also, if I want to turn the health symbol "Green" when I've changed the STATUS column to "Completed," how would I add that to the formula?

Please let me know if there are any questions. If there is a video or help article that explains this please let me know. I found lots of articles about health formulas dependent on % Complete, but that isn't my situation.

Thank you in advance!

Find more posts tagged with

Accepted answers

Mark Cronk

Hi Ali,

Does this work for you?

=IF[On Hold] = 1, "Gray", IF(OR([Due Date]@row >= TODAY(15), Status@row="Completed"), "Green", IF([Due Date]@row < TODAY(3), "Red", "Yellow")))

Mark

All comments

Mark Cronk

Hi Ali,

Does this work for you?

=IF[On Hold] = 1, "Gray", IF(OR([Due Date]@row >= TODAY(15), Status@row="Completed"), "Green", IF([Due Date]@row < TODAY(3), "Red", "Yellow")))

Mark

Ali Hoyt

@Mark Cronk Thank you so much! Yes, that worked. I had to slightly alter it to be the following, but I kept it basically the same as your suggestion. Thank you for your help! I really was at a loss for next steps and your suggestion helped incredibly.

=IF([On Hold]@row = 1, "Gray", IF(OR([Due Date]@row >= TODAY(15), Status@row = "Complete"), "Green", IF([Due Date]@row < TODAY(3), "Red", "Yellow")))

Mark Cronk

Happy to help. Glad you found a solution. Thank you for contributing to the Community.

Mark

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.