At Risk Formula that utilizes end date and status columns only...

I am stumped. I have tried various formulas only for them to error. The closest I have come is the following but I am missing pieces.

=IF([End Date]@row = "", 0, IF(AND([% Complete]@row < 1, [End Date]@row < TODAY()), 1))

BUT... I need it to do more:

I need it to NOT USE % Complete
I need it to flag at least 14 days prior to "End Date"
It needs to ignore BLANK dates
And it needs to capture only those line items with "In Progress" or "Not Started" Statuses

THANK YOU!!

Update: I also used the following with no luck:

=IF(AND([End Date]@row < TODAY(), ISDATE([End Date]@row, OR(Status@row = "In Progress", OR(Status@row = "Not Started", 1, 0)))))

Find more posts tagged with

Sheets

Support

Automations

Conditional Formatting

Workflows in Smartsheet

Accepted answers

All comments

Paul Newcome

Give this a whirl:

=IF([End Date]@row <> "", IF(AND(OR(Status@row = "In Progress", Status@row = "Not Started), [End Date]@row <= TODAY(14)), 1))

StephMac

Thank you, this came back unparseable as well.

Christian G.

=IF(AND([End Date]@row <= TODAY(14), ISDATE([End Date]@row), OR(Status@row = "In Progress", Status@row = "Not Started", 1, 0)))

This?

Paul Newcome

Are you able to provide a screenshot of the formula in the sheet similar to the screenshot below?

StephMac

Paul Newcome

It looks like there may be an extra space hidden somewhere in the column names. Instead of typing them out, click on the cell in the corresponding column in the same row so that it automatically populates the column name.

Christian G.

https://community.smartsheet.com/discussion/comment/348286#Comment_348286

@Paul Newcome How do you do that then the cell is coverd by the formula ? let's say I want to select the cell in a column next to the one with the formula ?

Andrée Starå

Hi @Christian G.

I hope you're well and safe!

Select another cell in the same column and manually adjust with @row or the correct row number.

Would that work/help?

I hope that helps!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Christian G.

@Andrée Starå It is what I was doing. I wondered if you guys had a different way to do it.

Andrée Starå

@Christian G.

Aha! No, unfortunately, I don't think there is another way.

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.