I need some help changing my At Risk formula so it doesn't flag when dates are empty

How can I change this formula so that if there are NO start dates and end dates in a row when it is set to Not Started, it does NOT show as flagged. Here is what it is currently doing:

Screen Shot 2024-03-25 at 8.38.01 AM.png

Here are the formulas I currently have set up for Status: =IF(ISBLANK(Complete@row), "", IF(Complete@row = "Not Started", "Gray", IF(Complete@row = "Complete", "Green", IF(AND([End Date]@row < TODAY(0), ISDATE([End Date]@row)), "Red", IF(AND([End Date]@row = TODAY(0), ISDATE([End Date]@row)), "Yellow", IF(OR(ISBLANK([End Date]@row), ISBLANK([Start Date]@row)), "Green", IF([End Date]@row <= TODAY(7), "Yellow", "Green")))))))

And the formula for At Risk: =IF([Helper at Risk]@row <> 1, IF(AND([End Date]@row <= TODAY(0), Complete@row <> "Complete", Status@row <> ""), 1, 0), IF(COUNTIFS(CHILDREN(), 1) > 0, 1, 0))

Find more posts tagged with

Formulas

Accepted answers

Scott Orsey

Hi @Alicia D, I would modify your At Risk formula... Here it is written out:

=IF(Complete@row = "Not Started", 0,IF([Helper at Risk]@row <> 1, IF(AND([End Date]@row <= TODAY(0), Complete@row <> "Complete", Status@row <> ""), 1, 0), IF(COUNTIFS(CHILDREN(), 1) > 0, 1, 0)))

In other words, set the flag to 0 if the project is Not Started. Otherwise use the same logic as before. I'm assuming the rest of the formula works as you would like it to.

Be well

All comments

Scott Orsey

Hi @Alicia D , Could you just add an IF() to the beginning of your At Risk formula like the following?

=IF(Complete@row = "Not Started", 0,....

=IF([Start Date]@row = "", 0,....

Hope this helps. Be well.

Alicia D

@Scott Orsey would that be before my at risk formula or before the completion formula?

Scott Orsey

Hi @Alicia D, I would modify your At Risk formula... Here it is written out:

=IF(Complete@row = "Not Started", 0,IF([Helper at Risk]@row <> 1, IF(AND([End Date]@row <= TODAY(0), Complete@row <> "Complete", Status@row <> ""), 1, 0), IF(COUNTIFS(CHILDREN(), 1) > 0, 1, 0)))

In other words, set the flag to 0 if the project is Not Started. Otherwise use the same logic as before. I'm assuming the rest of the formula works as you would like it to.

Be well

Alicia D

@Scott Orsey this worked, 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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.